Join or Union?

[DavidGraham167]

I have two tables:
Table 1:
Code,No1
A, 1
C, 2
E, 3

Table 2:
Code, No2
A, 2
B, 4
D, 6

I want to join these two tables to give;
Code,No1,No2
A, 1, 2
B, 0, 4
C, 2, 0
D, 0, 6
E, 3, 0

I have been trying varius JOINS & UNION but I cannot find the solution - any suggestions.
Thanks.

[FunkyDexter]

Looks like a Full Outer to me:-

Select isnull(table1.No1), isnull(table2.No2)
From Table1
Full Outer Join Table2
on Table1.Code = Table2.Code




edit> If you Joined a Union would it have to be a Left Join?

[DavidGraham167]

That's neary what I want, it's not giving the code
No1,No2
1, 2
0, 4
2, 0
0, 6
3, 0

I tried adding it to the SELECT satement but it leaves blanks.

Select table1.code, isnull(table1.No1), isnull(table2.No2)
From Table1
Full Outer Join Table2
on Table1.Code = Table2.Code

[Zvoni]

Worked with Access

SELECT table4.Code, table4.No1, iif(isnull(table2.No2),0,table2.No2) As No2 From
(SELECT DISTINCT(table3.Code), IIf(IsNull(table1.No1),0,table1.No1) AS No1
FROM (Select * From table1
UNION
Select * from table2)  AS table3 LEFT JOIN table1 ON table3.code = table1.code) As table4
left join table2 on table4.code=table2.code

[FunkyDexter]

Ah, sorry, I overlooked the code.

It will leave it blank for records that aren't in Table1 because you're reading it from Table1 and, well, it isn't there. What you want to do is read ot from Table1 if it's there and Table2 if its not, either:-
ISULL(Table1.Code, Table2.Code)
Or:-
Coalesce(Table1.Code, Table2.Code)

[DavidGraham167]

Thanks, I hadn't thought of using isnull that way.