CODEWITHSUNDEEP
sqloracle-databasejoinunion
Malvon
Malvon

Reputation: 1621

Left Outer Join and Union

I have two tables that have a parent and associated via a foreign key through their parent table, i.e.

TableP
 -----------------
| C1  | P2  | P3  |
 -----------------
   0    P2    P3

TableA
 -----------------
| C1  | A2  | A3  |
 -----------------
   0    A21   A31
   0    A22   A32

TableB
 -----------------
| C1  | B2  | B3  |
 -----------------
   0    B21   B31
   0    B22   B32

What I need is to pull the data in the following result set:

 ---------------------------------
| C1  |  A2  |  A3  |  B2  |  B3  |
 ---------------------------------
   0     A21   A31    NULL   NULL
   0     A22   A32    NULL   NULL
   0    NULL   NULL    B21    B31
   0    NULL   NULL    B22    B32

Is this possible through a combination of LEFT OUTER JOIN and UNION?

UPDATE:

Since one of the commenters answered the question but not in the form of an answer, here is the solution that worked for me:

SELECT p.c1, a.a2 a2, a.a3 a3, NULL b2, NULL b3 
FROM tablep p LEFT OUTER JOIN tablea a on a.c1 = p.c1 
UNION 
SELECT p.c1, NULL a2, NULL a3, b.b2 b2, b.b3 b3 
FROM tablep p LEFT OUTER JOIN tableb b on b.c1 = p.c1;

Upvotes: 1

Views: 11690

Answers (3)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

Assuming TableA.A2 is always not equal to TableB.B2

SELECT  tableP.C1,tableA.A2,tableA.A3,tableB.B2,tableB.B3
FROM tableA,tableB,tableP
WHERE tableP.C1 = tableA.C1
  AND tableP.C1 = tableB.C1
  And tableA.A2 = tableB.B2(+)
UNION ALL
SELECT  tableP.C1,tableA.A2,tableA.A3,tableB.B2,tableB.B3
FROM tableA,tableB,tableP
WHERE tableP.C1 = tableA.C1
  AND tableP.C1 = tableB.C1
  And tableB.B2 = tableA.A2(+)

Upvotes: 0

San
San

Reputation: 4538

Try this (not tested), make changes if there are some syntax errors.

select coalesce(taba.p1,tabb.p1), taba.a2, taba.a3, tabB.b2, tabB.b3
  from (select 'A'||to_char(rownum) RR, a2, a3, tablep.p1 p1
          from tablea JOIN TABLEP ON (tablea.p1 = tablep.p1)) taba
     FULL OUTER JOIN  
       (select 'B'||to_char(rownum) RR, b2, b3, tablep.p1 p1
          from tableb JOIN TABLEP ON (tableb.p1 = tablep.p1)) tabB
    on (taba.rr = tabB.rr and tabB.p1 = tabA.p1);

Upvotes: 0

Prakash Vishwakarma
Prakash Vishwakarma

Reputation: 832

Try this :

SELECT P.C1,A.A2,A.A3,B.B2,B.B3 FROM TableP P 
LEFT OUTER JOIN TableA A ON P.C1 = A.C1
LEFT OUTER JOIN TableB B ON P.C1 = B.C1

Have used Left Outer Join, as you asked for, no need of Union

Upvotes: 2

Related Questions