9

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

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

Finally, I have F which is a join table with many to one relationships with C, D and E. None of its fields (FKs to the other tables) are nullable.

I would like to write a SQL statement that joins all the tables in a single result set. I know I have to user outer joins because I want all of A returned regardless of whether or not it has children in B and similar with B and C.

Question One: I have been looking at the ANSI outer join syntax (I've only used Oracle "(+)" before) and cannot find an example that outer joins more than 2 tables. Can someone provide/point to an example?

Question Two: Is it possible to include records from tables D and E based on the join table F? If so, is this done with outer joins?

Thanks!

EDIT

Of course, right after I post this, I found an example that answers question 1. However, question 2 still has me stumped.

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
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
sdoca
  • 7,832
  • 23
  • 70
  • 127
  • You probably don't want FULL OUTER JOIN; it exists, but it is very seldom used (or useful). – Jonathan Leffler Sep 15 '10 at 21:00
  • You have not specified which columns in F join with each of the other tables - which means that noone can give a definitive answer. – Jonathan Leffler Sep 15 '10 at 21:02
  • Could you please post some sample data and the recordset you'd like to get? A's, B's an C's are not very informative names. – Quassnoi Sep 15 '10 at 21:02
  • @Jonathan, as I understand the ANSI joins (and I admit I'm just learning) I need to use a full join because I want all records from table A, even if there's no child records in table B AND all records from Table B, even if there's no foreign key to table A defined. A left or right join would only give me nulls for one direction of the relationship. Table F only has 3 fields which are foreign keys to tables C, D and E. Bill did a great job in his answer "drawing" the schema. – sdoca Sep 15 '10 at 22:44

4 Answers4

10

So I visualize your schema like this:

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

You can certainly do multiple joins, and you can also group join expressions with parentheses just like you can group arithmetic expressions.

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

These parentheses are not subqueries, they're just grouping join operations.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 2
    That's a great way to "draw" my schema. Thanks! I need to spend some time figuring out if your select statement does what I'm hoping, but I do think I need to use FULL joins as per my comment above to Jonathan. – sdoca Sep 15 '10 at 23:00
2

For clarification, the uppercase letters referred to tables and the lowercase to the primary/foreign key columns. I probably should have written it similar to Quassnoi, but will keep with this since that's how it started.

This SQL returns the results I'm loooking for:

         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

I tried to set up my SQL like Bill's but using FULL joins instead of LEFT ones, but it didn't return the same results as mine. I can't say that I fully understand his SQL, but the INNER joins filtered some of the results.

sdoca
  • 7,832
  • 23
  • 70
  • 127
1
 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 & F in there gets trickier:

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 put it all together:

 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
James Curran
  • 101,701
  • 37
  • 181
  • 258
0
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
Quassnoi
  • 413,100
  • 91
  • 616
  • 614