[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

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

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
mainrotor
Asked:
mainrotor
  • 2
2 Solutions
 
Scott PletcherSenior DBACommented:
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
 
mainrotorAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
"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
 
Steve WalesSenior Database AdministratorCommented:
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now