merge 2 tables - 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

merge 2 tables

Author

Topic

zoe2003
Starting Member

17 Posts

Posted - 2012-03-01 : 15:14:01

Hi all,

I have 2 tables:
this one:

user_id date sum
1 01/01/2000 20
1 01/02/2000 30
1 01/03/2000 40
2 01/04/2000 50
2 01/05/2000 60
2 01/06/2000 70

and this one:

date
01/01/2000
01/02/2000
01/03/2000
01/04/2000
01/05/2000
01/06/2000
01/07/2000
01/08/2000
01/09/2000
01/10/2000
01/11/2000
01/12/2000
1/13/2000

I want to merge them inyo one table as following :
user_id date sum
1 01/01/2000 20
1 01/02/2000 30
1 01/03/2000 40
1 01/04/2000 0
1 01/05/2000 0
1 01/06/2000 0
1 01/07/2000 0
1 01/08/2000 0
1 01/09/2000 0
1 01/10/2000 0
1 01/11/2000 0
1 01/12/2000 0
1 1/13/2000 0
2 01/01/2000 0
2 01/02/2000 0
2 01/03/2000 0
2 01/04/2000 50
2 01/05/2000 60
2 01/06/2000 70
2 01/07/2000 0
2 01/08/2000 0
2 01/09/2000 0
2 01/10/2000 0
2 01/11/2000 0
2 01/12/2000 0
2 1/13/2000 0

How can I do it using sql query ?

Thanks in advance.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-03-01 : 16:02:00

[code]
SELECT t1.user_id, t2.date, isnull(t1.sum, 0) [sum]
FROM table2 t2
LEFT JOIN
table1 t1
ON t1.date = t2.date;[/code]

zoe2003
Starting Member

17 Posts

Posted - 2012-03-01 : 16:25:35

with your query i get this results:

user_id date sum
1 01/01/2000 20
1 01/02/2000 30
1 01/03/2000 40
null 01/04/2000 0
null 01/05/2000 0
null 01/06/2000 0
null 01/07/2000 0
null 01/08/2000 0
null 01/09/2000 0
null 01/10/2000 0
null 01/11/2000 0
null 01/12/2000 0
null 1/13/2000 0
null 01/01/2000 0
null 01/02/2000 0
null 01/03/2000 0
2 01/04/2000 50
2 01/05/2000 60
2 01/06/2000 70
null 01/07/2000 0
null 01/08/2000 0
null 01/09/2000 0
null 01/10/2000 0
null 01/11/2000 0
null 01/12/2000 0
null 1/13/2000 0

I need that the user_id will appear in every row. like the example I gave. any ideas ?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-03-01 : 16:41:00

[code]
SELECT x.user_id, x.date, isnull(t1.sum, 0) [sum]
FROM (
SELECT distinct t1.user_id, t2.date
FROM #table2 t2
cross apply
#table1 t1
)x
LEFT JOIN
#table1 t1
On t1.user_id = x.user_id
And t1.date = x.date;[/code]

zoe2003
Starting Member

17 Posts

Posted - 2012-03-02 : 04:58:08

Thank you Russel, it's good !!!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-03-02 : 07:16:32

You're Welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources