table join problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

table join problem

Author

Topic

Joe.B
Starting Member

5 Posts

Posted - 2003-03-18 : 06:07:47

Hi there,
I need some help with a query. I don't know how to describe this, so i'll just put what i have and what i expect to have.


Forecast
CustomerI   ProductID    ForecastAmount
JOE             A             100
JOE             B             200
JOE             C             300

Orders
CustomerID  ProductID    Price   OrderAmount
JOE             A         100        10
JOE             B         200        20
JOE             D         400        40
---------------------------------------

ResultTable
CustomerID   ProductID   Price  OrderAmount
JOE              A         100      10
JOE              B         200      20
JOE              C         NULL     NULL
JOE              D         400      40 


Thnx in advance for any help.

Edited by - Joe.B on 03/18/2003 06:30:34

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-18 : 06:10:38

SELECT F.CustomerID, F.ProductID, O.Price, O.OrderAmount
FROM Forecast F LEFT JOIN Orders O ON F.CustomerID=O.CustomerID AND F.ProductID=O.ProductID

Joe.B
Starting Member

5 Posts

Posted - 2003-03-18 : 06:34:56

quote:

SELECT F.CustomerID, F.ProductID, O.Price, O.OrderAmount
FROM Forecast F LEFT JOIN Orders O ON F.CustomerID=O.CustomerID AND F.ProductID=O.ProductID

thnx for the answer.
There's still a problem here. I expect the result will show all the ProductID, but the query above only show 3 of them, that's A, B, and C

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-18 : 07:17:52

I believe when Rob said LEFT JOIN, what he really meant was FULL OUTER JOIN ....

SELECT F.CustomerID, coalesce(O.ProductID,F.ProductID), O.Price, O.OrderAmount
FROM Forecast F FULL OUTER JOIN Orders O ON F.CustomerID=O.CustomerID AND F.ProductID=O.ProductID

Jay White
{0}

Edited by - Page47 on 03/18/2003 07:18:43

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-18 : 07:20:16

Yes, o wise and psychic Jay, that is indeed what I meant.

Guess I've been spelling "full" wrong all these years.

Stoooooooopid publik edyoocayshun sistum.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-18 : 07:25:52

That must be how your collectable Wrox books spell it ....

Jay White
{0}

Joe.B
Starting Member

5 Posts

Posted - 2003-03-18 : 07:46:04

it works

... thanks to both of you ..
the query still result one null value for CustomerID, but i've fix it.
this "COALESCE" thing really usefull


SELECT  COALESCE (F.CustomerID, O.CustomerID), COALESCE (O.ProductID, 
                          F.ProductID), O.Price, O.OrderAmount
FROM     Forecast F FULL OUTER JOIN
         Orders O ON F.CustomerID = O.CustomerID AND F.ProductID = O.ProductID

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-18 : 08:37:12

Keep in mind that if you wish to filter the results of your query with a full outer join, you won't be able to use indexes due to the fact that all of the key fields are formulas.

I think full outer joins are logically kind of "iffy"; consider the following method that considers all customers and all products and then finds the records in either the forecast or the orders table that matches.

(note: i assume you have a table of customers and a table of products):

CROSS JOIN TECHNIQUE:

SELECT c.customerID, p.productID, o.price, o.orderamount, f.forecastamount
FROM customers c
CROSS JOIN productID p
LEFT OUTER JOIN Orders O
ON c.customerID = o.customerid and p.productID = o.productID
LEFT OUTER JOIN Forecast F
on c.customerId = f.customerId and p.productID = f.productID
WHERE
f.customerID is not null OR
p.customerID is not null

That intial query may be less efficient than the full outer join in some cases, but the resultset is still very indexable for further joining or filtering or querying.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources