Merging 2 tables in SQL

[ezypezy80]

Hi
I've got 2 tables to merge, but I want them to show the columns separately like this

Table_1 Table_2
A 10 A 20
D 10 D 20
G 40 F 30

Result table
A 10 20
D 10 20
F - 30
G 40 -


I tried full outer join and union but both ends up with some repeated rows.

 

[mrdenny]

You need a Left outer join.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[hooggie]

Not sure if this is the best way but I think it will work

select Col1, max(Col2), max(Col3) from (
select Col1, Col2, null as Col3 from Table1
union all
Select Col1, null, Col2 From Table2
) a
group by Col1

 

[ezypezy80]

Hi mrdenny,
A Left Outer Join would drop the "F" from Table 1 because
it has NULL value on the left table...

Result table
A 10 20
D 10 20
G 40 -

 

[chamilz]

how about this...?

SELECT a.Col1,a.Col2,b.Col2
FROM Table1 a
LEFT JOIN Table2 b
ON a.Col1 = b.Col1

UNION

SELECT b.Col1,a.Col2,b.Col2
FROM Table1 a
RIGHT JOIN Table2 b
ON a.Col1 = b.Col1

 

[nomadicalloy]

@ezypezy80 good answer.

This is Signature

http://www.xpherion.com/