Solved

I have a question regarding a JOIN query in SQL Server 2008

Posted on 2015-01-21
4
234 Views
Last Modified: 2015-01-30
Hi Experts,
I have two tables in my SQL Query Database (tblForecast, tblPurchaseOrders). I want to do a JOIN query between the two tables. I want to write a query that will return all the record in my tblForecast, even if there is no matching record in tblPurchaseOrders.  Below is a sample of the data stored in my table, as well as what I would my JOIN query to return.  Please let me know how to accomplish this.  Thank you in advance.


tblForecast sample data:
ItemNumber      Month      Year      ForecastQty
A10001            JAN      2015        1500
A10001            FEB      2015        2300
A10001            MAR      2015        3000


tblPurchaseOrders
ItemNumber      Month      Year      PurchaseOrderQty
A10001            FEB      2015        1250
A10001            APR      2015        5000


Desired outcome when I do my Join
The fields I want returned are all the fields from tblForecast, and only the Month, and PurchaseOrderQty field from tblPurchaseOrders.  The joining field is ItemNumber:

ItemNumber      Month      Year      ForecastQty      Month      PurchaseOrderQty
A10001            JAN      2015        1500            NULL      NULL
A10001            FEB      2015        2300            FEB      1250
A10001            MAR      2015        3000            NULL      NULL
A10001            NULL      NULL      NULL            APR      5000      

mrotor
0
Comment
Question by:mainrotor
  • 2
4 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40562541
What you need is a LEFT OUTER JOIN:

SELECT f..., po...
FROM tblForecast f
LEFT OUTER JOIN tblPurchaseOrders po ON ...

I think you're going to need to join on more than just ItemNumber of you'll get a lot of duplicate rows output.
0
 

Author Comment

by:mainrotor
ID: 40562579
I was thinking I would need to JOIN on ItemNumber, Month, and YEAR.  HOW do y ou do a JOIN on more that one field?

Thank you,
mrotor
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 40562599
"AND" the conditions:

SELECT f..., po...
FROM tblForecast f
LEFT OUTER JOIN tblPurchaseOrders po ON
    po.ItemNumber = f.ItemNumber AND
    po.Year = f.Year AND
    po.Month = f.Month
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 250 total points
ID: 40563036
Actually, I think you're going to need a Full Outer Join if there's a possibility that data could be non matching in either table:

Source:
create table tblForecast (ItemNumber char(6), [Month] char(3), [Year] char(4), ForecastQty int)
go
create table tblPurchaseOrders (ItemNumber char(6), [Month] char(3), [Year] char(4), PurchaseOrderQty int)
go

insert into tblForecast values ('A10001','JAN','2015',1500);
insert into tblForecast values ('A10001','FEB','2015',2300);
insert into tblForecast values ('A10001','MAR','2015',3000);

insert into tblPurchaseOrders values ('A10001','FEB','2015',1250);
insert into tblPurchaseOrders values ('A10001','APR','2015',5000);

Open in new window


The left join will not return the APR figures:
select a.ItemNumber, b.ItemNumber, a.[Month], a.[Year], a.ForecastQty, b.[Month], b.PurchaseOrderQty
from tblForecast a
left join tblPurchaseOrders b on a.ItemNumber = b.ItemNumber and a.[Month] = b.[Month] and a.[Year] = b.[Year]

ItemNumber ItemNumber Month Year ForecastQty Month PurchaseOrderQty
---------- ---------- ----- ---- ----------- ----- ----------------
A10001     NULL       JAN   2015 1500        NULL  NULL
A10001     A10001     FEB   2015 2300        FEB   1250
A10001     NULL       MAR   2015 3000        NULL  NULL

Open in new window


A full join will:
select isnull(a.ItemNumber, b.ItemNumber) as ItemNumber, a.[Month], a.[Year], a.ForecastQty, b.[Month], b.PurchaseOrderQty
from tblForecast a
full join tblPurchaseOrders b on a.ItemNumber = b.ItemNumber and a.[Month] = b.[Month] and a.[Year] = b.[Year]

ItemNumber Month Year ForecastQty Month PurchaseOrderQty
---------- ----- ---- ----------- ----- ----------------
A10001     JAN   2015 1500        NULL  NULL
A10001     FEB   2015 2300        FEB   1250
A10001     MAR   2015 3000        NULL  NULL
A10001     NULL  NULL NULL        APR   5000

Open in new window

0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

813 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