Problem returning ALL entries from table

I have the following problem when i create SQL queries and have never found out how to do this.

We have tables which hold all Inventory item details (IV00101/ IV00102) and a table which holds data for sales etc (tblKPIData). What i need to do is query these tables showing ALL inventory items whether or not they have a sale to display total sales for that item and the qty currently on hand, below is the query i come up with, but this only shows items that have had sales, i need to see all items even if they have 0 sales;

SELECT     tblKPIData.ItemNumber, VJL.dbo.IV00101.ITEMDESC, SUM(tblKPIData.CustomerPrice) AS Sales, VJL.dbo.IV00102.QTYONHND AS [Qty on Hand]
FROM         VJL.dbo.IV00101 INNER JOIN
                      VJL.dbo.IV00102 ON VJL.dbo.IV00101.ITEMNMBR = VJL.dbo.IV00102.ITEMNMBR LEFT OUTER JOIN
                      tblKPIData ON VJL.dbo.IV00102.ITEMNMBR = tblKPIData.ItemNumber
WHERE     (tblKPIData.DOCDATE > CONVERT(DATETIME, '2007-04-03 00:00:00', 102)) AND (VJL.dbo.IV00102.LOCNCODE = 'HO')
GROUP BY tblKPIData.ItemNumber, VJL.dbo.IV00101.ITEMDESC, VJL.dbo.IV00102.QTYONHND, tblKPIData.PriceGroup
ORDER BY tblKPIData.PriceGroup, tblKPIData.ItemNumber

This works, but as said above, just shows items that have sales. I need to show ALL items from IV00101 / IV00102, is this possible?
ale1981Asked:
Who is Participating?
 
Nick UpsonPrincipal Operations EngineerCommented:
this

WHERE     (tblKPIData.DOCDATE > CONVERT(DATETIME, '2007-04-03 00:00:00', 102)) AND (VJL.dbo.IV00102.LOCNCODE = 'HO')

needs to add become

WHERE     (tblKPIData.DOCDATE > CONVERT(DATETIME, '2007-04-03 00:00:00', 102) or tblKPIData.DOCDATE is NULL) AND (VJL.dbo.IV00102.LOCNCODE = 'HO')

as in the final results any column that would come from tblKPIData but doesn't due to there being no sales will have a value of NULL
0
 
dportasCommented:
Remove this from the WHERE clause:

tblKPIData.DOCDATE > CONVERT(DATETIME, '2007-04-03 00:00:00', 102)

and put it in the ON clause instead.

BTW, you can safely remove the CONVERT function if you use the ISO standard date format for your date literals. Either include the "T" delimiter or remove all the delimiters:

tblKPIData.DOCDATE > '2007-04-03T00:00:00'

or

tblKPIData.DOCDATE > '20070403'
0
 
ale1981Author Commented:
Sorry, I am quite new to more complex SQL statements, well this is for me anyway, JOINS etc!

Where in the query would i put;

tblKPIData.DOCDATE > CONVERT(DATETIME, '2007-04-03 00:00:00', 102)

instead of where it is now?

Thanks, will remove the CONVERT() function ;)
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
elec1celeCommented:
it could either be a seperate condition in the on caluse that joins your outer table like

OUTER JOIN
                      tblKPIData ON (VJL.dbo.IV00102.ITEMNMBR = tblKPIData.ItemNumber
and   (tblKPIData.DOCDATE > CONVERT(DATETIME, '2007-04-03 00:00:00', 102)))

or you could change you where clause the way Nickupson suggested.

The reason it needs to be in the ON clause of the outer join is cause when it is in the where clause all the entries w/o sales have a NULL value there which makes the where clause return false for all those rows.  The other where clause works cause it test for the NULL case seperatly so it gets included also.
0
 
ale1981Author Commented:
I tried to change the WHERE clause as NickUpson suggested, but this returns all the Item Numbers and Sales SUM as NULL, this is not as i expected? Here is what i changed the query to;

SELECT     tblKPIData.ItemNumber, VJL.dbo.IV00101.ITEMDESC, SUM(tblKPIData.CustomerPrice) AS Sales, VJL.dbo.IV00102.QTYONHND AS [Qty on Hand]
FROM         VJL.dbo.IV00101 INNER JOIN
                      VJL.dbo.IV00102 ON VJL.dbo.IV00101.ITEMNMBR = VJL.dbo.IV00102.ITEMNMBR LEFT OUTER JOIN
                      tblKPIData ON VJL.dbo.IV00102.ITEMNMBR = tblKPIData.ItemNumber
WHERE     (tblKPIData.DOCDATE > CONVERT(DATETIME, '2007-04-03 00:00:00', 102) OR
                      tblKPIData.DOCDATE IS NULL) AND (VJL.dbo.IV00102.LOCNCODE = 'HO')
GROUP BY tblKPIData.ItemNumber, VJL.dbo.IV00101.ITEMDESC, VJL.dbo.IV00102.QTYONHND, tblKPIData.PriceGroup
ORDER BY tblKPIData.PriceGroup, tblKPIData.ItemNumber

0
 
Nick UpsonPrincipal Operations EngineerCommented:
any calculation that includes a null will return a null, hence sum () ruturned null

make that part into    SUM(ifnull(tblKPIData.CustomerPrice,0.0))
0
 
ale1981Author Commented:
Thanks Nick,

What about the ItemNumbers? These are returning as NULL also if the Sales is NULL?
0
 
ale1981Author Commented:
I have adjusted the query slightly and it now works :)

SELECT     VJL.dbo.IV00101.ITEMNMBR, VJL.dbo.IV00101.ITEMDESC, SUM({ fn IFNULL(tblKPIData.CustomerPrice, 0.0) }) AS Sales,
                      VJL.dbo.IV00102.QTYONHND AS [Qty on Hand]
FROM         VJL.dbo.IV00101 INNER JOIN
                      VJL.dbo.IV00102 ON VJL.dbo.IV00101.ITEMNMBR = VJL.dbo.IV00102.ITEMNMBR LEFT OUTER JOIN
                      tblKPIData ON VJL.dbo.IV00102.ITEMNMBR = tblKPIData.ItemNumber
WHERE     (tblKPIData.DOCDATE > CONVERT(DATETIME, '2007-04-03 00:00:00', 102) OR
                      tblKPIData.DOCDATE IS NULL) AND (VJL.dbo.IV00102.LOCNCODE = 'HO')
GROUP BY VJL.dbo.IV00101.ITEMDESC, VJL.dbo.IV00102.QTYONHND, tblKPIData.PriceGroup, VJL.dbo.IV00101.ITEMNMBR
ORDER BY VJL.dbo.IV00101.ITEMNMBR, tblKPIData.PriceGroup

Thanks for your help, points will be spread over the solution i used.

Thanks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.