Solved

I need help with a LEFT JOIN in SQL Server 2008

Posted on 2014-09-19
7
404 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 49

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 40

Expert Comment

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

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

726 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