Solved

I need help with a LEFT JOIN in SQL Server 2008

Posted on 2014-09-19
7
392 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 39

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 167 total points
Comment Utility
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
Comment Utility
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 45

Accepted Solution

by:
Vitor Montalvão earned 167 total points
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
Vitor at minimum you have to do the PONumber and the ITEMNMBR.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQl query 19 12
IN with @variable 5 14
Mssql SQL query 14 20
Sql query 34 14
In this article I will describe the Detach & Attach 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.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

763 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

8 Experts available now in Live!

Get 1:1 Help Now