Join both ways...? - 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

SQL Server Development (2000)

Join both ways...?

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2005-08-10 : 10:41:03

Hi people,

I need to sum up and join data from two tables. Lets say I run a warehouse for fruit. On any given day some fruit comes in and some goes out. I need to get the total of each fruit in the same result set like this:

Fruit           In           Out
--------------------------------
Apples          40            6
Bananas          0           31
Pares           36           65
Oranges         16            0
Melons           0           31

DDL:

DECLARE @tblIn table (Fruit varchar(15), Amount int)
DECLARE @tblOut table (Fruit varchar(15), Amount int)

INSERT INTO @tblIn 
SELECT 'Apples', 15 UNION ALL SELECT 'Apples', 25 UNION ALL
SELECT 'Pares', 5 UNION ALL SELECT 'Oranges', 16 UNION ALL
SELECT 'Pares', 31  

INSERT INTO @tblOut
SELECT 'Apples', 6 UNION ALL SELECT 'Bananas', 25 UNION ALL
SELECT 'Pares', 65 UNION ALL SELECT 'Bananas', 6 UNION ALL
SELECT 'Melons', 31 

SELECT Fruit, SUM(Amount) FROM @tblIn GROUP BY Fruit
SELECT Fruit, SUM(Amount) FROM @tblOut GROUP BY Fruit

How is this possible? I tried with some derived tables and stuff but my mind seems off today...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-10 : 10:50:52

This should do it... excelent question asking!


Select 
	Fruit = coalesce(A.Fruit,B.Fruit),
	InCnt = sum(isnull(A.Amount,0)),
	OutCnt = sum(isnull(B.Amount,0))
From @tblIn A
Full Join @tblOut B
On A.Fruit = B.Fruit
Group by coalesce(A.Fruit,B.Fruit)

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-10 : 10:58:45

mmmmmmmmmmmmm....melons.....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-10 : 11:01:27

not really a fan of the melon family... but i love the peaches and pears

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2005-08-10 : 11:22:14

Coool...nver used coalesce or full join until now...quite cool :) Thanx alot buddy...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-10 : 11:26:36

coalesce or isnull should work... and I only recently needed a full join

glad to help

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

magesh
Starting Member

23 Posts

Posted - 2005-08-13 : 10:34:13

Hi corey..

have u tested that query..
what will happen if out table contains more than one
value for apple ??

the query posted by u will yeild different results..

try with mutiple derived tables

Magesh

magesh
Starting Member

23 Posts

Posted - 2005-08-15 : 23:35:29

Hi all,

this may be very very late reply.. :)

here is the query..

SELECT tbl1.Fruits,ISNULL(tbl2.FruitsIN,0) AS 'FruitsIN',ISNULL(tbl3.FruitsOUT,0) AS 'FruitsOUT' FROM
(SELECT COALESCE(A.Fruit,B.Fruit) AS 'Fruits'
FROM @tblIn a FULL JOIN @tblOut b
ON a.Fruit = b.Fruit
GROUP BY COALESCE(A.Fruit,B.Fruit)) tbl1
LEFT OUTER JOIN
(SELECT Fruit, SUM(Amount) AS 'FruitsIN' FROM @tblIn GROUP BY Fruit) tbl2
ON tbl1.Fruits=tbl2.Fruit
LEFT OUTER JOIN
(SELECT Fruit, SUM(Amount) AS 'FruitsOUT' FROM @tblOut GROUP BY Fruit) tbl3
ON tbl1.Fruits=tbl3.Fruit

Magesh

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2005-08-16 : 03:39:53

You're actually correct magesh...I just tried it and it seems that the sum of apples both in and out is doubled when I add another record for apples in the out-table. Yours is (for obvious reasons) a little less elegant but more importantly: it yields the right result

Thanx bud...and welcome to sqlteam!

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-08-16 : 04:11:47

[code]Select fruit, sum(inCount), sum(outCount)
from (
select fruit, amount as inCount, 0 as outCount
from tblIn
union all
select fruit, 0 as inCount, amount as outCount
from tblOut
) as t
group by fruit[/code]I adore melons :)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2005-08-16 : 04:36:47

Hehe I just love posting here...one brilliant solution after the other

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources