summing, join not working - 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

General SQL Server Forums

New to SQL Server Programming

summing, join not working

Author

Topic

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2010-03-11 : 11:16:34

I am trying to combine 2 tables together while summing anything in matching rows. I gather all information in temp1 table and temp2 table and now need to combine these together but joining isn't getting all the data (even with LEFT JOIN!).
temp1
col1,col2,col3
xx,yy,50
xxx,yy,100

temp2
col1,col2,col3
xx,yy,50
xxx,yyy,100

combined - I can't get it like this
col1,col2,col3
xx,yy,100
xxx,yy,100
xxx,yyy,100

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-11 : 11:24:19

[code]SELECT col1,col2,SUM(col3) AS col3
FROM
(
SELECT col1,col2,col3 FROM temp1
UNION ALL
SELECT col1,col2,col3 FROM temp2
)t
GROUP BY col1,col2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-11 : 11:26:44

use full outer join.
and use
isnull(t1.col3,0) + isnull(t2.col3,0) as col3

No, you're never too old to Yak'n'Roll if you're too young to die.

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2010-03-11 : 13:24:19

So I have it working for the most part, but am kinda stuck on another join question. When I am joining a table, how can I restrict the table to join only the rows which exist in table1, but still show all rows from table1. INNER JOIN/RIGHT JOIN/JOIN all fail because they don't show all the rows from table1, just the rows that exist in both tables. LEFT JOIN/FULL OUTER JOIN fail because they show more rows than exist in table1.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-11 : 14:20:34

select t1.*,t2.* from table1 t1 left join table2 t2 on t1.col=t2.col

gives you all records from t1 and returns null for columns of t2 in select list if there is no match in t2

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-12 : 10:45:54

quote:
Originally posted by detlion1643

So I have it working for the most part, but am kinda stuck on another join question. When I am joining a table, how can I restrict the table to join only the rows which exist in table1, but still show all rows from table1. INNER JOIN/RIGHT JOIN/JOIN all fail because they don't show all the rows from table1, just the rows that exist in both tables. LEFT JOIN/FULL OUTER JOIN fail because they show more rows than exist in table1.

LEFT JOIN will show only all rows from table1 unless you've some kind of 1 to many relationship existing b/w the tables in which case it will return more rows.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources