full join 3 tables

[positivethinking]

Hello all

I am working with oracle 10i

I need to join 3 tables in 1 sql statements. (please see attached)
it looks simple, but i have spent many hours without any luck,
each table have 3 identical columns and several different columns i need to join all the tables and all the records from each table.

i have tried the full join in 2 tables but i get all records from 1 table and only the repiting records from the second table, but i am not getting the no matching recors from the second table.
Of corse in reality these tables are much more complex, but this will give me an idea.
Please help, any suggestion is well appreciated. Thanks a lot

[rski]

So you need full outer join for these three tables?

[magicwand]

you mean something like this ?

select case when a.myid is not null then a.myid
when a.myid is null and b.myid is not null then b.myid
when a.myid is null and b.myid is null and c.myid is not null then c.myid
else null
end myid
,case when a.subj is not null then a.subj
when a.subj is null and b.subj is not null then b.subj
when a.subj is null and b.subj is null and c.subj is not null then c.subj
else null
end subj
,case when a.tube is not null then a.tube
when a.tube is null and b.tube is not null then b.tube
when a.tube is null and b.tube is null and c.tube is not null then c.tube
else null
end tube
,case when a.site is not null then a.site
when a.site is null and b.site is not null then b.site
when a.site is null and b.site is null and c.site is not null then c.site
else null
end site
,an1
,an2
,an3
,an4
,an5
,an6
,an7
from tst.roche_a a
full outer join tst.roche_b b on (a.myid = b.myid and a.subj = b.subj and a.tube = b.tube and a.site = b.site)
full outer join tst.roche_c c on (a.myid = c.myid and a.subj = c.subj and a.tube = c.tube and a.site = c.site)
order by myid nulls last
;