CODEWITHSUNDEEP
sqloracle-database
Kate
Kate

Reputation: 285

combine three tables in oracle sql

I'm having these three tables

Table A:

code        aname
----------- ----------
1           A
2           B
3           C

Table B: 

code        bname
----------- ----------
1           aaa
1           bbb
2           ccc
2           ddd

Table C

code        cname
----------- ----------
1           xxx  
1           yyy
1           zzz
2           www

How can I achieve the output like this using single query ?

code        aname      bname       cname
----------- ---------- ----------  ----------
1           A          aaa         xxx
1           A          bbb         yyy
1           A          NULL        zzz
2           B          ccc         www
2           B          ddd         NULL
3           C          NULL        NULL

Any suggestions ?

Thanks

Upvotes: 1

Views: 164

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

It looks like you want "lists" to be vertical for tables B and C. This is doable, by using row_number(). However, the trick is getting the third row in where there are no matches.

Here is one method. It uses a full outer join to combine the b and c names together. It then uses left join to bring in the a records.

select a.code, a.name, bc.bname, bc.cname
from a left join
     (select coalesce(b.code, c.code) as code, bname, cname
      from (select code, bname, NULL as cname,
                    row_number() over (partition by code order by code) as seqnum
            from b
           ) b full outer join
           (select code, NULL as bname, cname,
                    row_number() over (partition by code order by code) as seqnum
            from c
           ) c
           on b.code = c.code and b.seqnum = c.seqnum
      ) bc
      on bc.code = a.code;

Upvotes: 3

Related Questions