Solved

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

Posted on 2015-01-21
4
221 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:ScottPletcher
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:
ScottPletcher 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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

705 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

19 Experts available now in Live!

Get 1:1 Help Now