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?