Geek Answers Handbook

How to make Outer Join on> 2 Tables (Oracle)

I'm not sure how to describe my table structure, so hope this makes sense ...

I have 3 tables in a hierarchical relationship, so A has a one-to-many relationship with B, which in turn has a one-to-many relationship with C. The trick is that the foreign key in B and C is allowed as null (i.e. not defined by the parent). I also have D and E with no relation to A, B or C (directly).

Finally, I have F, which is a multi-to-one join table with C, D, and E. None of its fields (FKs for other tables) allow values.

I would like to write an SQL statement that joins all tables in one result set. I know that I have external external connections of the user, because I want all A to return regardless of whether he has children in B and similar to B and C.

Question 1: I looked at the syntax of the ANSI outer join (I used to use Oracle "(+)") and cannot find an example that outer joins more than two tables. Can someone point / point an example?

Question two: Is it possible to include entries from tables D and E based on join table F? If so, is this done with external connections?

Thank!

EDIT

Of course, right after I posted this, I found an example that answers question 1. However, question 2 still puzzles me.

Example:

         SELECT A.a,
                B.b,
                C.c
           FROM A
FULL OUTER JOIN B ON B.a = A.a
FULL OUTER JOIN C ON C.b = B.b
+5
4

, - / . , , , Quassnoi, , .

SQL , :

         SELECT A.a,
                B.b,
                C.c,
                D.d,
                E.e
           FROM A
FULL OUTER JOIN B ON B.a = A.a
FULL OUTER JOIN C ON C.b = B.b
FULL OUTER JOIN F ON F.c = C.c
FULL OUTER JOIN D ON D.d = F.d
FULL OUTER JOIN E ON E.e = F.e

SQL Bill, FULL- LEFT, , . , SQL, INNER .

0

, :

A --o< B --o< C --< F >-- D
                      >-- E

, , , , .

SELECT ...
FROM A LEFT OUTER JOIN (
  B LEFT OUTER JOIN (
    C LEFT OUTER JOIN (
      F INNER JOIN D ON D.d = F.d
        INNER JOIN E ON E.e = F.e
      ) ON C.c = F.c
    ) ON B.b = C.b
) ON A.a = B.a

, .

+9
 select a.*, b.*, c.*
 from a
 left outer join b on a.b_id = b.id
 left outer join c on a.c_id = c.id

Now getting D, E, and F there becomes more complicated:

select c.*, d.*, e.*
from C
inner join f on c.id = f.c_id
inner join d on d.id = f.d_id
inner join e on d.id = f.e_id

Then we will share it all:

 select a.*, b.*, cde.*
 from a
 left outer join b on a.b_id = b.id
 left outer join 
 (select c.id as c_id, c.*, d.*, e.*
   from C
   inner join f on c.id = f.c_id
   inner join d on d.id = f.d_id
   inner join e on d.id = f.e_id) CDE
 on a.c_id = cde.c_id
+1
source 
SELECT  a.*, b.*, c.*, d.*, e.*
FROM    a
LEFT JOIN
        b
ON      b.a = a.id
LEFT JOIN
        
ON      c.b = b.id
LEFT JOIN
        f
ON      f. = c.id
LEFT JOIN
        d
ON      d.id = f.d
LEFT JOIN
        e
ON      e.id = f.e
0
source

More articles:


All Articles