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:

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

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

8 Years
Discussion Span
Last Post by Geekitygeek

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.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.