Solved

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

Posted on 2015-01-21
4
237 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

821 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