Solved

I need help with a LEFT JOIN in SQL Server 2008

Posted on 2014-09-19
7
406 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 50

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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 40

Expert Comment

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

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

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

717 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