SQL outer join statement?

[marks2007]

Hi,

I have a problem about SQL outer join

I have two tables

-----------
table one

id value1

1 a
2 b
3 c

-------------
table two

id value2
2 d
4 e
5 f

---------------------
I like get the results

id value1 value2
1 a 0
2 b d
3 c 0
4 0 e
5 0 f

* if no value put 0 .I am using SQL Server 2005 now

I try left join ,right join and full join, but I can not get the results.Please help me.

Thanks for help

Mark

 

[Brianwarnock]

You need to use a UNION query, see Help,but the SQL is

SELECT Table1.id, Table1.value1 AS expr1, nz([value2],0) AS expr2
FROM Table1 LEFT JOIN Table2 ON Table1.id = Table2.id;
union select table2.id, nz([value1],0) as expr1, table2.value2 as expr2
FROM Table1 right JOIN Table2 ON Table1.id = Table2.id;

Brian

Just noticed you are using SQL server I coded for ACCESS don't know if they are the same.