I need help joining the two queries below, and possible using a LEFT JOIN to get my desired result.
I need to display all open purchase orders and the qty_ordered for each of the orders. I also need to show the qty_received on each of those purchase orders (this value comes from a separate table).
Not all Open Purchase orders exist in the Inventory table. When I join the two queries below, I want to show all open purchase orders, and if they don't exist in the Inventory then they should display a 0 in the quanity received column. This is why i think a LEFT JOIN is needed.
The two tables should be joined using the PONUMBER field.
Below I have provide samples of both of my queries along with their respective results. I have also included a sample of the desired outcome.
QUERY #1 SAMPLE
QUERY #1 OUTPUT:
SELECT I.PONUMBER , I.[ITEMNMBR], SUM(I.[QTYORDER]) AS [QTYOrdered]
FROM POHeader as P
inner join PODetail as I on P.PONUMBER = I.PONUMBER
where I.[POLNESTA] not in (4,5,6) and P.[DOCDATE] >= '1/1/2014'
and I.POTYPE =1
GROUP BY I.PONUMBER ,I.[ITEMNMBR]
ORDER BY I.[ITEMNMBR]
QUERY #2 SAMPLE:
PONUMBER ITEMNMBR QTYOrdered
07254 IJK789 780
07465 XYZ432 1250
07503 EFG456 536
07895 ABC123 100
QUERY #2 OUTPUT:
SELECT A.PONUMBER, A.[ITEMNMBR], SUM(A.QTYRECVD) as [QTYReceived]
FROM INVENTORY as A
WHERE A.[DATERECD] >= '1/1/2014' and A.RCPTSOLD = 0
GROUP BY A.PONUMBER, A.[ITEMNMBR]
ORDER BY A.PONUMBER
DESIRED OUT COME AFTER JOINING BOTH OF THE ABOVE QUERIES:
PONUMBER ITEMNMBR QTYReceived
07465 XYZ432 550
07503 EFG456 300
PONUMBER ITEMNMBR QTYOrdered QTYReceived
07254 IJK789 780 0
07465 XYZ432 1250 550
07503 EFG456 536 300
07895 ABC123 100 0