JOIN PROBLEM - 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

JOIN PROBLEM

Author

Topic

luyichen
Starting Member

2 Posts

Posted - 2009-07-23 : 12:15:54

Hi All,

I have two tables as following:

Table1
ID QUAN
1 a
2 b
3 c
4 d
5 e

Table2
ID2 NUM
2 b
3 c
4 d
6 F
7 G

I want to the result as:

ID QUANT NUM
1 a
2 b b
3 c c
4 d d
5 e
6 F
7 G

ANY IDEA? THANKS

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-23 : 12:22:18

[code]
SELECT t1.ID, t1.QUANT, t2.NUM
FROM table1 t1
LEFT JOIN
table2 t2
On t1.ID = t2.ID;
[/code]

luyichen
Starting Member

2 Posts

Posted - 2009-07-23 : 12:36:33

Thanks for the left join.

But I want to show all the row of the two table, and make the ID column one. Say if ID=5 is on table1, ID=9 is on table2, I want the new table to show ID=5 and ID=9 in the same column, list their Columns seperately.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-23 : 12:38:00

change LEFT JOIN to FULL OUTER JOIN

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-23 : 13:44:55

[code]SELECT
COALESCE(t1.ID, t2.ID2) AS ID,
COALESCE(t1.QUANT, t2.NUM) AS QUANT,
CASE WHEN t1.QUANT IS NOT NULL THEN t2.NUM ELSE NULL END AS Num
FROM
@table1 t1
FULL OUTER JOIN
@table2 t2
ON t1.ID = t2.ID2[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources