This discussion is archived
11 Replies Latest reply: Mar 31, 2011 11:20 PM by 533433 RSS

Outer join two tables with two keys

pj*433620*ng Newbie
Currently Being Moderated
Hi,
I have a question regard outer join,

I have table A (Aid, ADesc), Table B(Bid, Bdesc), and Table C (Aid, Bid, Cdesc)
There is data in Table A and B, but Table C is empty
I want to outer join C with A and B with below query

select ADesc,Bdesc, Cdest
from A, B, C
where A.Aid=C.Aid(+) and B.Bid(+)=C.Bid
and A.Aid='XXX' and B.Bid(+)='ZZZ'

The query result show only data in column Adesc , but column BDesc is empty even though there are some data in table B.

How should the query been modified to correctly show ADESC and Bdesc.

Thanks

Vincent
  • 1. Re: Outer join two tables with two keys
    533433 Journeyer
    Currently Being Moderated
    Firstly, the key in table C is Bid or Aid as you seem to have used both C.Aid and C.Bid.

    Secondly, you may need to remove the condition "and B.Bid(+)='ZZZ'" if you want the the values of Bdesc to be displayed as well. But I am not sure whether this will actually affect your business logic.

    Cheers --
  • 2. Re: Outer join two tables with two keys
    pj*433620*ng Newbie
    Currently Being Moderated
    Hi,
    The table C consist of 2 keys, Aid refer to table A and Bid refer to table B.
    The condition "B.Bid(+)='ZZZ'"cannot be removed due to business logic

    thanks for reply

    Vincent
  • 3. Re: Outer join two tables with two keys
    533433 Journeyer
    Currently Being Moderated
    Can you probably share some sample data? With the data that I roughly constructed, I am able to get all the values using your query as below:
    with a as
    (select 'XXX' aid, 'adesc' adesc from dual),
    b as
    (select 'ZZZ' bid, 'bdesc' bdesc from dual),
    c as
    (select 'XXX' aid, 'ZZZ' bid, 'cdest' cdest from dual)
    select ADesc,Bdesc, Cdest
    from A, B, C
    where A.Aid=C.Aid(+) and B.Bid(+)=C.Bid
    and A.Aid='XXX' and B.Bid(+)='ZZZ'
    
    Output:
    "ADESC"     "BDESC"     "CDEST"
    "adesc"     "bdesc"     "cdest"
  • 4. Re: Outer join two tables with two keys
    pj*433620*ng Newbie
    Currently Being Moderated
    Hi, APNL
    My Table C is an empty table, that's why I outer join it with others two table.

    Rgds

    Vincent
  • 5. Re: Outer join two tables with two keys
    584428 Newbie
    Currently Being Moderated
    Hi,

    Can you try like this-:

    SELECT * FROM a FULL JOIN c ON a.aid = c.aid
    ,
    b FULL JOIN c ON b.bid = c.bid
    WHERE a.aid = 'XXX'
  • 6. Re: Outer join two tables with two keys
    wood_pusher Explorer
    Currently Being Moderated
    what is the essence of doing an outer join with an empty table? but if you insists it should be like this
    SELECT ADESC,BDESC, C1.CDESC
    FROM A,  B, C C1, C C2
    where A.Aid=C1.Aid(+) and B.Bid = C2.Bid(+)
    AND A.AID='XXX' AND B.BID(+)='ZZZ'
  • 7. Re: Outer join two tables with two keys
    pj*433620*ng Newbie
    Currently Being Moderated
    hi,
    The purpose for the query is:
    I have one table C and it have to column Aid and Bid which refer to two different master table A and B.
    I want to show the column CDESC in table C if the Aid and Bid in table C found the key in table A and B, or else null should be return.

    The SQL suggested by you
    SELECT ADESC,BDESC, C1.CDESC
    FROM A, B, C C1, C C2
    where A.Aid=C1.Aid(+) and B.Bid = C2.Bid(+)
    AND A.AID='XXX' AND B.BID(+)='ZZZ'

    will show CDESC even though Bid in table C are no match to Bid in table B

    Thanks.

    Vincent
  • 8. Re: Outer join two tables with two keys
    wood_pusher Explorer
    Currently Being Moderated
    there will always be no match in tables A and C and B and C because C is an empty table.
  • 9. Re: Outer join two tables with two keys
    533433 Journeyer
    Currently Being Moderated
    Guess the below should work:

    Case when records are available in Table C:
    with a as
    (select 'XXX' aid, 'adesc' adesc from dual),
    b as
    (select 'ZZZ' bid, 'bdesc' bdesc from dual),
    c as
    (select 'XXX' aid, 'ZZZ' bid, 'cdest' cdest from dual where 1 = 1)
    select ADesc,Bdesc, C1.Cdest
    from 
    A LEFT OUTER JOIN C c1 ON (A.AID = C1.AID AND A.AID = 'XXX'), 
    B LEFT OUTER JOIN C C2 ON (B.BID = C2.BID AND B.BID = 'ZZZ'); 
    
    Output:
    "ADESC"     "BDESC"     "CDEST"
    "adesc"     "bdesc"     "cdest"
    Case when table C is empty:
    with a as
    (select 'XXX' aid, 'adesc' adesc from dual),
    b as
    (select 'ZZZ' bid, 'bdesc' bdesc from dual),
    c as
    (select 'XXX' aid, 'ZZZ' bid, 'cdest' cdest from dual where 1 = 2)
    select ADesc,Bdesc, C1.Cdest
    from 
    A LEFT OUTER JOIN C c1 ON (A.AID = C1.AID AND A.AID = 'XXX'), 
    B LEFT OUTER JOIN C C2 ON (B.BID = C2.BID AND B.BID = 'ZZZ'); 
    
    "ADESC"     "BDESC"     "CDEST"
    "adesc"     "bdesc"     ""
  • 10. Re: Outer join two tables with two keys
    pj*433620*ng Newbie
    Currently Being Moderated
    Hi, APNL
    It's work fine if the table C is empty or the aid and bid in table C both match data in table a, table b.
    But what if only aid in Table C match the key in Table A but not bid?

    i.e. C = (select 'XXX' aid 'YYY' bid, 'cdesc' cdesc from dual)

    the cdesc should be empty also when the query run..

    Thanks for your reply

    Vincent
  • 11. Re: Outer join two tables with two keys
    533433 Journeyer
    Currently Being Moderated
    Try This,
    with a as
    (select 'XXX' aid, 'adesc' adesc from dual),
    b as
    (select 'ZZZ' bid, 'bdesc' bdesc from dual),
    c as
    (select 'XXX' aid, 'YYY' bid, 'cdest' cdesc from dual)
    select ADesc,Bdesc, CASE WHEN C1.Cdesc IS NULL OR C2.Cdesc IS NULL THEN NULL ELSE C1.Cdesc END Cdesc
    from 
    A LEFT OUTER JOIN C c1 ON (A.AID = C1.AID AND A.AID = 'XXX'), 
    B LEFT OUTER JOIN C C2 ON (B.BID = C2.BID AND B.BID = 'ZZZ')
    
    Output:
    "ADESC"     "BDESC"     "CDESC"
    "adesc"     "bdesc"     ""
    Edited by: APNL on Mar 31, 2011 11:15 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points