Solved

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

Posted on 2015-01-21
4
242 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
[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
  • 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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

729 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