Hi, this is giving me a major headache and my boss wants the project wrapped up asap : /

I have two views pulling data from several tables. The end result is this:

CustomerTotals
CustomerID numeric(18,0),
ProductID numeric(18,0),
Delivered numeric(5,0),
Ordered numeric(5,0)

SupplierTotals
SupplierID numeric(18,0),
ProductID numeric(18,0),
Delivered numeric(5,0),
Ordered numeric(5,0)

CustomerTotals shows total of orders we have recieved and items we have sent out. SupplierTotals shows the total we have ordered from our suppliers and how many we have so far received.

I need to calculate some values from these but i tried to create the following view and each row is duplicated : /

SELECT     ISNULL(st.ProductID, ct.ProductID) AS 'ID', ISNULL(st.Ordered, 0) - ISNULL(st.Delivered, 0) AS [On Order], ISNULL(st.Delivered, 0) - ISNULL(ct.Delivered, 0) 
                      AS [In Stock], ISNULL(ct.Ordered, 0) - ISNULL(ct.Delivered, 0) AS [Orders Outstanding], ISNULL(st.Ordered, 0) - ISNULL(ct.Ordered, 0) AS Unassigned
FROM         dbo.SupplierTotals AS st FULL OUTER JOIN
                      dbo.CustomerTotals AS ct ON st.ProductID = ct.ProductID

Can anyone shed some light on where i'm going wrong? C#.net is my strong suit...SQL is still a very alien language to me lol

Doh, figured it out...one of the view was returning multiple lines so the join had duplicates. i've fixed this now..sorry for wasting anyones time..like i said, SQL is still a bit hit and miss for me :)

Thanks to anyone who took the time to read this.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.