Join to gather all data - 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)

Join to gather all data

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-02-21 : 07:46:07

jansci writes "hello,

i'm a newbie to sql programming but one type of query that perplexed me was how to do the following:
Table1
Date Ship
---- ----
2/1 45
2/3 87
2/4 23

Table2
Date Rcvd
---- ----
2/1 34
2/2 48
2/3 33
2/5 55

Final_Table
Date Ship Rcvd
---- ---- ----
2/1 45 34
2/2 48 null
2/3 87 33
2/4 23 null
2/5 null 55

I've tried all the types of joins and where clauses but cant come up with something like the Final_Table.

Hope to hear from you soon!

Thanks!"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2007-02-21 : 07:47:42

You didn't try the FULL JOIN, that will do the trick:

SELECT COALESCE(T1.Date, T2.Date) AS Date, T1.Ship, T2.Rcvd
FROM Table1 T1 FULL JOIN Table2 T2 ON T1.Date=T2.Date

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-21 : 08:22:18

[code]-- prepare sample data
declare @table1 table (date varchar(3), ship tinyint)

insert @table1
select '2/1', 45 union all
select '2/3', 87 union all
select '2/4', 23

declare @table2 table (date varchar(3), rcvd tinyint)

insert @table2
select '2/1', 34 union all
select '2/2', 48 union all
select '2/3', 33 union all
select '2/5', 55

-- FULL JOIN
SELECT COALESCE(T1.Date, T2.Date) AS Date,
SUM(T1.Ship) AS Ship,
SUM(T2.Rcvd) AS Rcvd
FROM @Table1 AS T1
FULL JOIN @Table2 AS T2 ON T2.Date = T1.Date
GROUP BY COALESCE(T1.Date, T2.Date)
ORDER BY 1

-- UNION ALL
SELECT Date,
SUM(Ship) AS Ship,
SUM(Rcvd) AS Rcvd
FROM (
SELECT Date,
Ship,
0 AS Rcvd
FROM @Table1
UNION ALL
SELECT Date,
0,
Rcvd
FROM @Table2
) AS d
GROUP BY Date
ORDER BY 1[/code]
Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources