CODEWITHSUNDEEP
sqloracle-database
muhnizar
muhnizar

Reputation: 327

Combine different table with same column without ambigous

I have query problem. I have 3 table.

table A 
    ----------------------------
    NAME   | CODE
    ----------------------------
    bob    | PL
    david  | AA
    susan  | PL
    joe    | AB


table B 
    ----------------------------
    CODE | DESCRIPTION
    ----------------------------
    PL   | code 1
    PB   | code 2 
    PC   | code 3

table C 
    ----------------------------
    CODE | DESCRIPTION
    ----------------------------
    AA   | code 4
    AB   | code 5 
    AC   | code 6

Table B and C have unique row. the result I need :

    ----------------------------
    NAME   | CODE | DESCRIPTION
    ----------------------------
    bob    | PL   | code 1
    david  | AA   | code 4
    susan  | PL   | code 1
    joe    | AB   | code 5

What I have tried so far

http://sqlfiddle.com/#!9/ffb2eb/9

Upvotes: 2

Views: 44

Answers (2)

Take_Care_
Take_Care_

Reputation: 2154

I think UNION will make this. And additionally It will also remove duplicates if some will exists. 

SELECT  A.NAME , UN.CODE ,UN.DESCRIPTION 
FROM A,
     (SELECT CODE,DESCRIPTION FROM B 
      UNION 
     SELECT CODE,DESCRIPTION FROM C ) UN 
WHERE A.CODE = UN.CODE;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271141

You are close. I think you just need COALESCE():

select A.*, coalesce(B.DESCRIPTION, C.DESCRIPTION) as description
from A left join
     B
     on A.CODE = B.CODE left join
     C
    on A.CODE = C.CODE
order by A.NAME;

Upvotes: 3

Related Questions