November 29, 2011 at 12:23 pm
I have a database where I am trying to draw summery data for a simple display.
If I have data in the all the tables, the query works, if I have no data in the items (and as such the log table) the query returns zero rows. I have a count on the item table and a sum on the log table. If I remove the log table, then I get the expected result, but the sum of the mass is needed from the log table when there is data.
The query that fails ooks like this.
SELECT tblPurchasesHeader.PUH_ID, tblPurchasesBody.PUB_ID, tblItemDesc.ITD_ItemDesc AS Description, tblPurchasesBody.PUB_ITDID,
tblItemDesc.ITD_ProductID AS Product, tblPurchasesBody.PUB_Grading AS Grading, tblPurchasesBody.PUB_Ordered AS Ordered,
ISNULL(COUNT(tblItems.ITE_ID), 0) AS Counted, SUM(tblAuditLog.AUD_Mass) AS Mass
FROM tblAuditLog INNER JOIN
tblItems ON tblAuditLog.AUD_ItemCode = tblItems.ITE_ItemCode RIGHT OUTER JOIN
tblPurchasesHeader INNER JOIN
tblPurchasesBody ON tblPurchasesHeader.PUH_ID = tblPurchasesBody.PUB_PUHID INNER JOIN
tblItemDesc ON tblPurchasesBody.PUB_ITDID = tblItemDesc.ITD_ID ON tblItems.ITE_PurchaseReference = tblPurchasesBody.PUB_ID AND
tblItems.ITE_ITDID = tblPurchasesBody.PUB_ITDID
WHERE (tblAuditLog.AUD_LocationFrom < 0) AND (tblPurchasesHeader.PUH_ID = 294)
GROUP BY tblPurchasesBody.PUB_ITDID, tblPurchasesBody.PUB_Grading, tblItemDesc.ITD_ProductID, tblItemDesc.ITD_ItemDesc,
tblPurchasesHeader.PUH_ID, tblPurchasesBody.PUB_ID, tblPurchasesBody.PUB_Ordered
ORDER BY Description
The one without the log table
SELECT tblPurchasesHeader.PUH_ID, tblPurchasesBody.PUB_ID, tblItemDesc.ITD_ItemDesc AS Description, tblPurchasesBody.PUB_ITDID,
tblItemDesc.ITD_ProductID AS Product, tblPurchasesBody.PUB_Grading AS Grading, tblPurchasesBody.PUB_Ordered AS Ordered,
ISNULL(COUNT(tblItems.ITE_ID), 0) AS Counted
FROM tblItems RIGHT OUTER JOIN
tblPurchasesHeader INNER JOIN
tblPurchasesBody ON tblPurchasesHeader.PUH_ID = tblPurchasesBody.PUB_PUHID INNER JOIN
tblItemDesc ON tblPurchasesBody.PUB_ITDID = tblItemDesc.ITD_ID ON tblItems.ITE_PurchaseReference = tblPurchasesBody.PUB_ID AND
tblItems.ITE_ITDID = tblPurchasesBody.PUB_ITDID
WHERE (tblPurchasesHeader.PUH_ID = 294)
GROUP BY tblPurchasesBody.PUB_ITDID, tblPurchasesBody.PUB_Grading, tblItemDesc.ITD_ProductID, tblItemDesc.ITD_ItemDesc,
tblPurchasesHeader.PUH_ID, tblPurchasesBody.PUB_ID, tblPurchasesBody.PUB_Ordered
ORDER BY Descriptionand it returns
PUB_IDDescriptionPUB_ITDIDProductGradingOrderedCounted
362HIND QUARTER24BA3100
How can I modify the first query to give me the sum of masses (zero) if there are no records in the item table, rather than give me a emty result set.
Do I need to look at a stored proc of function or is there a way to do this is T-SQL.
November 29, 2011 at 2:00 pm
Add the AuditLog table back to the end of the from clause with an OUTER JOIN, like so:
RIGHT JOIN tblAuditLog ON tblItems.ITE_ItemCode = tblAuditLog.AUD_ItemCode
That should work.
November 29, 2011 at 5:15 pm
Hylton,
Please in the future post the table definitions, some sample data and the expected results.
It looks to me like the query was created using some sort of query designer tool. I've personally never used a RIGHT OUTER JOIN in 12 years of developing SQL. I've only seen others use them twice. I find a RIGHT JOIN hard to conceptualize on what the query is doing. Query designers like MS Access and Crystal Reports will create them. It also looks like the tables were created using some ORM tool since the column names all have a table prefix on them.
In any case, it's a very common mistake to put a limiting filter in the WHERE clause on a table that's in an OUTER JOIN. This causes the JOIN to act like an INNER JOIN - no rows returned unless the WHERE condition is met.
Your posted query was awful to read and understand due to formatting and some odd syntax from the query designer tool used. I tried to clean it up somewhat and make it easier to understand.
Here is an example - hopefully correct since there was no posted scripts to create and populate the tables:
SELECT
PH.PUH_ID, PB.PUB_ID, ITM.ITD_ItemDesc AS Description
, PB.PUB_ITDID, ITM.ITD_ProductID AS Product
, PB.PUB_Grading AS Grading, PB.PUB_Ordered AS Ordered
, ISNULL(COUNT(IT.ITEID, 0)) AS Counted
, ISNULL(SUM(AL.AUD_Mass, 0)) AS Mass
FROM tblPurchasesHeader PH
INNER JOIN tblPurchasesBody PB ON
PH.PUH_ID = PB.PUB_PUHID
INNER JOIN tblItemDesc ITD ON
PB.PUB_ITDID = ITD.ITD_ID
LEFT JOIN tblItems IT ON
PB.PUB_ID = IT.ITE_PurchaseReference
AND PB.PUB_ITDID = IT.ITE_ITDID
LEFT JOIN tblAuditLog AL ON
IT.ITE_ItemCode = AL.AUD_ItemCode
AND AL.AUD_LocationFrom < 0 -- Has to be part of the LEFT JOIN
-- If part of the WHERE then it acts
-- like an INNER JOIN
WHERE PH.PUH_ID = 294
GROUP BY PB.PUB_ITDID, PB.PUB_Grading, ITD.ITD_ProductID
, ITD.ITD_ItemDesc, PH.PUH_ID, PB.PUB_ID, PB.PUB_Ordered
There is another way to do this using the APPLY operator:
SELECT
PH.PUH_ID, PB.PUB_ID, ITM.ITD_ItemDesc AS Description
, PB.PUB_ITDID, ITM.ITD_ProductID AS Product
, PB.PUB_Grading AS Grading, PB.PUB_Ordered AS Ordered
, ISNULL(ALSum.ItemCount, 0) AS Counted
, ISNULL(ALSum.SumMass, 0) AS Mass
FROM tblPurchasesHeader PH
INNER JOIN tblPurchasesBody PB ON
PH.PUH_ID = PB.PUB_PUHID
INNER JOIN tblItems IT ON
PB.PUB_ID = IT.ITE_PurchaseReference
AND PB.PUB_ITDID = IT.ITE_ITDID
INNER JOIN tblItemDesc ITD ON
IT.ITDID= ITD.ITD_ID
OUTER APPLY
(SELECT COUNT(*) AS ItemCount
, SUM(AL.AUD_Mass) AS AUD_Mass
FROM tblAuditLog AL
WHERE AL.AUD_ItemCode = IT.ITE_ItemCode
) AS ALSum
Todd Fifield
November 29, 2011 at 9:12 pm
Thank you for taking the time to read through and reply.
Unfortunately adding
RIGHT JOIN tblAuditLog ON tblItems.ITE_ItemCode = tblAuditLog.AUD_ItemCode
To the end of the from clause returns the same results as having it in the front. When there is no data in the items and log file the query returns no rows.
Hylton
November 29, 2011 at 9:22 pm
Todd,
Thank you for the time you spent on this.
Yes, the query was generated/formatted using Microsoft SQL Server Management Studio.
Your comments about posting sensible questions with sufficient data are appreciated. I will keep this in mind.
I was able to get the first sample that you posted working and it gives the required results. I will need to spend a bit more time on the second sample as I do not fully understand it, before I am able to get it working.
Just out of curiosity, which of the two will be the more efficient.
Hylton
November 29, 2011 at 9:34 pm
Todd, you are absolutely right. You did a lot of work to clean up that code. It does read like something generated by an ORM tool and a query designer. I've never used RIGHT JOINs, either. I always use LEFT JOINs.
The reason for using LEFT JOINs is because it is easy to get confused with the JOIN direction. An OUTER JOIN returns all records from one table and all matching records from the other table. The direction of the JOIN controls which table returns all records and which table returns only matching records.
Personally, I can never remember which table is considered the RIGHT table and which is considered the LEFT table. So, I always write my JOIN clauses the same way:
FROM [Table1] LEFT JOIN [Table2] ON [Table1].[JoinField] = [Table2].[JoinField]
I list the tables in the same order in the FROM - JOIN portion of the clause as I do in the ON - = portion of the clause. In this case, I don't have to stop to figure it out every time I read it: all records will be returned from Table1 (the LEFT side) and matching records will be returned from Table2.
I will bet that the problem with the query above is the order of the JOIN clauses. Try following Todd's recommendations. Pull the Audit table out of the WHERE clause just to see what happens. Where you re-wrote the query with the Audit table at the end of the FROM -JOIN clauses, change that to a LEFT JOIN and see what happens.
If you are still having a problem, work your way through Todd's post, and give us more data to work with. I'll bet you will figure it out on your own, though.
November 30, 2011 at 11:33 am
Hylton Tregenza (11/29/2011)
Todd,Thank you for the time you spent on this.
Yes, the query was generated/formatted using Microsoft SQL Server Management Studio.
Your comments about posting sensible questions with sufficient data are appreciated. I will keep this in mind.
I was able to get the first sample that you posted working and it gives the required results. I will need to spend a bit more time on the second sample as I do not fully understand it, before I am able to get it working.
Just out of curiosity, which of the two will be the more efficient.
Hylton
Hylton,
Glad you got it working. As far as which will be more efficient - well, it depends. Read up on the different types of JOINs. Gail Shaw wrote some excellent articles on her blog and I think they were also posted on this forum - just search for them. Also read up on the APPLY operator. IIRC Paul White wrote some excellent articles on this.
That being said you can think of APPLY like a FOR/EACH type construct. For each row in the main query the APPLY is applied. That only supports LOOP type joins. If the number of matching rows in the audit log table is relatively small per row in the main query, it will be about as fast as anything else. If the number of rows in the audit log is let's say 1000 per row in the main query, then it would probably be slower.
You will have to try it yourself and see which performs better.
Todd Fifield
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply