I have created a small example to demonstrate
select * from filea
<pre>KEYA FLDA
1 1
2 2
3 3
5 5 </pre>
select * from fileb
<pre>KEYB FLDB
1 1
2 2
4 4
6 6 </pre>
select * from filec
<pre>KEYC FLDC
1 1
3 3
4 4
7 7 </pre>
select * from filea
full outer join fileb on keya = keyb
full outer join filec on keya = keyc or keyb = keyc
<pre>KEYA FLDA KEYB FLDB KEYC FLDC
1 1 1 1 1 1
2 2 2 2 – –
3 3 – – 3 3
5 5 – – – –
– – 4 4 4 4
– – 6 6 – –
– – – – 7 7 </pre>
select coalesce(keya,keyb,keyc) key, coalesce(flda,’0′) flda,
coalesce(fldb,’0′) fldb, coalesce(fldc,’0′) fldc
from filea
full outer join fileb on keya = keyb
full outer join filec on keya = keyc or keyb = keyc
<pre>KEY FLDA FLDB FLDC
1 1 1 1
2 2 2 0
3 3 0 3
5 5 0 0
4 0 4 4
6 0 6 0
7 0 0 7 </pre>
<i>Note that in my sample tables all fields were defined as character even though I have entered numeric text, hence the ‘0’ in the coalesce statements.</i>
Discuss This Question: 17  Replies