?
Solved

I need help with a LEFT JOIN in SQL Server 2008

Posted on 2014-09-19
7
Medium Priority
?
407 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 668 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 664 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 51

Accepted Solution

by:
Vitor Montalvão earned 668 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 40

Expert Comment

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

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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.
Suggested Courses

800 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