2

I, also, have a question regarding how to join my database tables. My data setup is as follows:

MainTable
main_id x y z
1       l m n
2       o p q
SubTableA
main_id extra_id foo bar
1       a        1   4
1       b        2   h
1       c        1   g
2       a        k   er
2       b        k   34
2       c        l   f

SubTableB
main_id extra_id a b c
1       a        v r j
1       c        w r k
2       c        x h l

SubTableC
main_id extra_id mono stereo
2       c        n    null
2       d        z    y

I'm trying to achieve the following result: 

main_id x y z extra_id foo bar a b c mono stereo
1       l m n a        1   4   v r j null null
1       l m n b        2   h         null null
1       l m n c        1   g   w r k null null
2       o p q a        k   er        null null
2       o p q b        k   34        null null
2       o p q c        l   f   x h l n    null
2       o p q d                      z    y

To spice things up, sometimes the extra_id is null or one of the subtables doesn't contain main_id but then I would like the other tables to be joined and null-filled for values that aren't found.
In that case I would like the outcome to be:
main_id x y z extra_id foo bar a b c null null for example.

I tried to use How can I join multiple SQL tables using the IDs? as template but couldn't get it all the way.

This is what I tried so far: 

SELECT
  m.*,
  a.*,
  b.*,
  c.*,
FROM
  MainTable m,
  SubTableA a,
  SubTableB b,
  SubTableC c,
WHERE
  m.main_id = a.main_id(+)
  AND a.main_id=b.main_id(+)
  AND a.extra_id=b.extra_id(+)
  AND a.main_id = c.main_id(+)
  AND a.extra_id = c.extra_id(+);

Edit:
I want all rows from main table. When they match sub tables on main_id join them for each of the extra_id (which can be more than one for each main_id). And if combination of main_id and extra_id exists in more than one sub table I want them to be joined. Will give more elaborated example when in front of laptop again.

SQLFiddle

Thanks,
Christian

Community
  • 1
  • 1
Christian Karlsson
  • 101
  • 1
  • 11

2 Answers2

2

This answer assumes you want left joins, not right joins - in other words, always show rows from MainTable even if there are no related rows in SubTableA and the others. This is implied by the sample output with null values for mono and stereo.

I'd move to the newer join syntax, and when you want a column included you should name it rather than relying on SELECT *. SELECT * is fine for quick, ad-hoc queries, but for anything more you should name the columns.

I'd also recommend aliasing to cut down on typing. It looks like you were doing that, given the SC and AD in your example.

SELECT
  m.main_id,
  m.x,
  m.y,
  m.z,
  a.extra_id,
  a.foo,
  a.bar,
  b.a,
  b.b,
  b.c,
  c.mono,
  c.stereo
FROM MainTable m
LEFT JOIN SubTableA a
  ON m.main_id = a.main_id
LEFT JOIN SubTableB b
  ON a.main_id = b.main_id AND a.extra_id = b.extra_id
LEFT JOIN SubTableC c
  ON a.main_id = b.main_id AND a.extra_id = c.extra_id
Ed Gibbs
  • 25,924
  • 4
  • 46
  • 69
1

Let's look at your joins. You want to join Main to A, but keep all the rows that exist in A but don't have a main_id, right? So you want everything from the RIGHT side of the join.

MainTable M RIGHT JOIN SubTableA A ON M.main_id = A.main_id

And you want to do this will all tables;

SELECT
    A.*
    , B.*
    , C.*
FROM
    MainTable M
     RIGHT JOIN SubTableA A ON M.main_id = A.main_id
     RIGHT JOIN SubTableB B ON M.main_id = B.main_id
     RIGHT JOIN SubTableC C ON M.main_id = C.main_id

WHERE
[some stuff]

Note: You may want to try RIGHT OUTER JOINs. Note2: I was confused by the description. If the row exists in Main but not in a sub-table then you want LEFT - all rows in Main regardless of whether they exist on the other side of the join. If the row exists in a sub-table but not Main then you want a RIGHT OUTER.

P.S. I didn't realize this was for Oracle, so I hope it's not too different.

BClaydon
  • 1,900
  • 2
  • 20
  • 34