November 25, 2007 at 7:25 pm
Here is my current SQL:
SELECT OrderLine.ProductNo, Category.Description, SUM(OrderLine.QtyOrdered) AS [Total Ordered]
FROM OrderLine
INNER JOIN Product
ON Product.ProductNo = OrderLine.ProductNo
INNER JOIN Category
ON Category.CategoryID = Product.CategoryID
GROUP BY OrderLine.ProductNo, Category.Description
I need to include products that have not been ordered yet. So I believe that I need to use LEFT OUTER JOIN instead of INNER JOINs, but when I change them both to LEFT OUTER JOINs I get the same result, like wise when I change either of then to LEFT OUTERs and leave the other as an INNER. It seems to be a simple mistake that I am not seeing, but I am new to SQL and cant figure it out. If someone could please help me out and show me how to fix this query so that it will display nulls I would greatly appreciate it.
November 25, 2007 at 11:47 pm
SELECTP.ProductNo, C.Description, COALESCE( SUM( O.QtyOrdered ), 0 ) AS [Total Ordered]
FROMProduct P
INNER JOIN Category C ON C.CategoryID = P.CategoryID
LEFT JOIN OrderLine O ON P.ProductNo = O.ProductNo
GROUP BY P.ProductNo, C.Description
--Ramesh
November 26, 2007 at 4:51 am
If all else fails, change the order of your tables. @=)
Sometimes with INNER JOIN table order doesn't matter, but I usually go from the table that's likely to have the most records down to the table likely to have the least records. In Outer Joins, however, depending on what Outer you use (right or left) order definitely matters.
When designing a join, look at it from the perspective of "Table A will be my starting point (in the FROM clause) because the record selection criteria from that table will always be 'true'." Does that make sense?
November 26, 2007 at 9:50 am
Ramesh's query should give you the result that you want.
Brandie is correct in discussing table order. If you just change the Inner Joins to Left Outer Joins in your original query you are in essence saying, "Give me all the Orders, the Products if there is one associated with the Order, and all the Categories, if there is one associated with the product". In order to get the answer you want (all products even if not ordered) without changing your table order you would want this:
OrderLine Right Outer Join Product On Product.ProductNo = OrderLine.ProductNo Inner Join Category On Category.CategoryId = Product.CategoryId
I prefer Ramesh's query for readability, even though both queries should product the same execution plan.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 26, 2007 at 1:30 pm
Thank you all so much for explaining why my query wasnt working and how to fix it, I tried Ramesh's query and it worked perfectly. My only question is what does COALESCE do?
November 26, 2007 at 1:57 pm
COALESCE is a function that takes 2 to n arguments and returns the first non-null value. So in Ramesh's example the SUM(OrderLine.QtyOrdered) for any products that have no orders will return Null. By embedding the SUM in the COALESCE function the query will return a 0 for the quantity ordered for any products that have no orders. This makes the data clearer and also means that the UI will not have to handle a null value.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 26, 2007 at 2:58 pm
OK, that makes sense to place that in the query. Thank you.
November 27, 2007 at 3:51 am
You can also use SUM(ISNULL(Quantity,0)) to do something similar. The difference is that ISNULL simply replaces any NULL values with zero instead of looking for the first non-NULL value.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply