JOIN on a result set - 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 2005 Forums

Transact-SQL (2005)

JOIN on a result set

Author

Topic

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2010-10-28 : 16:50:34

I have three tables that I need to join with SUM():

Table1
ID Name
(ID is PK)

Table2
ID QTY
(ID has multiple records)

Table3
ID QTYOF
(ID has multiple records)

I would like to join these tables so that I just have 1 record per ID and the total SUM of QTY and the total SUM of QTYOF.

SELECT
t1.ID
, CASE WHEN SUM(t2.QTY) IS NULL THEN 0 ELSE SUM(t2.QTY)) END
, CASE WHEN SUM(t3.QTYOF) IS NULL THEN 0 ELSE SUM(t3.QTYOF)) END
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
LEFT JOIN Table3 t3 ON t1.ID = t3.ID
GROUP BY t1.ID

With this query however I get duplicated values for the QTY colummns. I think I need to do a "SELECT AS" for the first join and then join on Table3 but I'm not sure how to do that.
Any help is appreciated.

xholax
Starting Member

12 Posts

Posted - 2010-10-28 : 17:01:57

select t1.id , t2.QTY, t3.QTYOF from t1
inner join (
select t2.id , SUM(t2.QTY) as QTY from table2 t2
group by t2.id
) t2
on t2.id = t1.id
inner join (
select t3.id , SUM(t3.QTYOF) as QTYOF from table2 t3
group by t3.id
) t3
on t3.id=t1.id
order by t1.id

xholax
Starting Member

12 Posts

Posted - 2010-10-28 : 17:10:57

or this :

select t1.id ,
(CASE WHEN t2.QTY IS NULL THEN 0 ELSE t2.QTY END) AS QTY,
(CASE WHEN t3.QTYOF IS NULL THEN 0 ELSE t3.QTYOF END) AS QTYOF
from t1
left outer (
select t2.id , SUM(t2.QTY) as QTY from table2 t2
group by t2.id
) t2
on t1.id =t2.id
left outer (
select t3.id , SUM(t3.QTYOF) as QTYOF from table2 t3
group by t3.id
) t3
on t1.id=t3.id
order by t1.id

xholax
Starting Member

12 Posts

Posted - 2010-10-28 : 17:12:36

sorry i forgot the wor join at the above query:

select t1.id ,
(CASE WHEN t2.QTY IS NULL THEN 0 ELSE t2.QTY END) AS QTY,
(CASE WHEN t3.QTYOF IS NULL THEN 0 ELSE t3.QTYOF END) AS QTYOF
from t1
left outer join(
select t2.id , SUM(t2.QTY) as QTY from table2 t2
group by t2.id
) t2
on t1.id =t2.id
left outer join (
select t3.id , SUM(t3.QTYOF) as QTYOF from table2 t3
group by t3.id
) t3
on t1.id=t3.id
order by t1.id

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2010-10-28 : 20:51:56

Awesome thanks - works perfectly!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources