Solved

I need help with a LEFT JOIN in SQL Server 2008

Posted on 2014-09-19
7
398 Views
Last Modified: 2014-11-06
Hi Experts,
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).

NOTE:  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
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]

Open in new window


QUERY #1 OUTPUT:
PONUMBER  	ITEMNMBR  	QTYOrdered
07254		IJK789		780
07465		XYZ432		1250
07503		EFG456		536
07895		ABC123		100

Open in new window


QUERY #2 SAMPLE:
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

Open in new window


QUERY #2 OUTPUT:
PONUMBER  	ITEMNMBR  	QTYReceived
07465		XYZ432		550
07503		EFG456		300

Open in new window




DESIRED OUT COME AFTER JOINING BOTH OF THE ABOVE QUERIES:
PONUMBER  	ITEMNMBR  	QTYOrdered	QTYReceived
07254		IJK789		780		        0
07465		XYZ432		1250			550
07503		EFG456		536		       300
07895		ABC123		100		       0

Open in new window

0
Comment
Question by:mainrotor
7 Comments
 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 167 total points
ID: 40332780
try the below:
SELECT D.PONUMBER , D.[ITEMNMBR],  SUM(D.[QTYORDER]) AS [QTYOrdered], sum (isnull, inv.QTYRECVD,0) as QtyReceived
FROM POHeader as P
inner join  PODetail as D on  P.PONUMBER = D.PONUMBER
LEFT JOIN INVENTORY as inv on D.PONUMBER = inv.PONUMBER and D.ITEMNMBR = Inv.ITeMNMBR 
-- not sure if these 2 are needed because they should be filtered out by the POs you're getting, but I left them in.
and  Inv.[DATERECD] >= '1/1/2014'  and Inv.RCPTSOLD = 0
where I.[POLNESTA] not in (4,5,6) and P.[DOCDATE] >= '1/1/2014'                                         
and D.POTYPE =1
GROUP BY D.PONUMBER ,D.[ITEMNMBR]
ORDER BY D.[ITEMNMBR]

Open in new window

0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 166 total points
ID: 40332784
I would use a CTE, because it makes it easier to debug:

with tblI as
(
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]),

tblA as
(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])

select I.PONUMBER, I.[ITEMNMBR], SUM(QTYOrdered) AS QTYOrdered, sum(QTYReceived) as QTYReceived
FROM tblI
LEFT JOIN tblA
ON tblI.PONUMBER = tblA.PONUMBER
0
 
LVL 46

Accepted Solution

by:
Vitor Montalvão earned 167 total points
ID: 40332797
Yes, you just need to add the 2nd query to the 1st with LEFT JOIN:
SELECT I.PONUMBER , I.[ITEMNMBR],  SUM(I.[QTYORDER]) AS [QTYOrdered], SUM(A.QTYRECVD) as [QTYReceived]
FROM POHeader as P
    INNER JOIN PODetail as I ON P.PONUMBER = I.PONUMBER
    LEFT JOIN INVENTORY as A ON P.PONUMBER = A.PONUMBER
WHERE I.[POLNESTA] NOT IN (4,5,6)
  AND P.[DOCDATE] >= '1/1/2014'                                         
  AND I.POTYPE =1
  AND A.[DATERECD] >= '1/1/2014'
  AND A.RCPTSOLD = 0
GROUP BY I.PONUMBER ,I.[ITEMNMBR]
ORDER BY I.[ITEMNMBR]

Open in new window

0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40332815
Vitor at minimum you have to do the PONumber and the ITEMNMBR.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40332824
What you mean "to do"?
By his example, PONumber and ITEMNMBR are grouped, means that you only need to join on PONumber to get the same Itemnmbr.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40333951
Kyle,

You have a small typo:
This:
WHERE   I.[POLNESTA] NOT IN (4, 5, 6)
Should be:
WHERE   D.[POLNESTA] NOT IN (4, 5, 6)
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40339382
Hi Anthony,

You are correct, I re-aliased the table so it would make more sense, but copied the where condition from the OP.  Must have missed that one.  

Thanks for pointing it out.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now