Oracle joining multiple tables?

Question

I am trying to do the following

  select
   TA.C1 ,TB.C1 ,TC.C1
   from  TableA TA ,TableB TB , TableC TC  
   where TA.C1 = "ABC"
   AND TA.C2 = TB.C1
   and TA.C3 = TC.C1

Result is

enter image description here

My aim is to add a couple more tables to this query

  select
   TA.C1,TB.C1,TC.C1,TD.C1,TE.C1
   from  TableA TA ,TableB TB , TableC TC , TableD TD, TableE TE
   where TA.C1 = "ABC"
   and TA.C2 = TB.C1
   and TA.C3 = TC.C1
   and TA.C4 = TD.C1
   and TD.C2 = TE.C1

But since the Column TD.C1 contains null values , whereas TA.C4 always has some values , I get the below results.

enter image description here

The expected result is

enter image description here

I have tried joining using Joins for joining 4 tables :

select
     TA.C1,TB.C1,TC.C1,TD.C1
     from  TableA TA          
     JOIN TableB TB ON (TA.C2 = TB.C1)
     JOIN TableC TC ON (TA.C3 = TC.C1)
     LEFT JOIN TableD TD ON (TA.C4 = TD.C1)
     AND TA.C1 = "ABC"

The results is pretty near what I expect:

enter image description here

The issue is I'm not sure how to Join the 5th table (Table E) as this doesn't have any joing with Table A.

I do not understand your problem. You do need a **direct link** from `A` to `E`. All you need is a `path` from `A` to `E`. And apparently you do have it: `A`->`D`->`E`. — PM 77-1, Apr 10 '14 at 02:15
@PM77-1 Mate I'm unsure how to Join D and E (I know the coomand TD.C2 = TE.C1 ) , but not sure how to put it in query — misguided, Apr 10 '14 at 02:21
Just add another `LEFT OUTER JOIN` (as shown in `shree.pat18`'s answer below) unless you know for sure that you have a corresponding `E` value for each `C` value. In such case even `INNER JOIN` will be fine. — PM 77-1, Apr 10 '14 at 02:26
I'm a little confused. If `TA.C3 = "ABC"`, `TC.C1` should also be equal to "ABC". Then how are you getting the other rows in the result set? Or am I missing something? — shree.pat18, Apr 10 '14 at 02:27
@misguided Hey, my bad. For some reason I kept thinking of it as a `WHERE` instead of `AND` for that condition. My apologies. — shree.pat18, Apr 10 '14 at 02:40
Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right [sic] table column to be not NULL after a LEFT JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/q/4707673/3404097) — philipxy, Apr 22 '19 at 03:09
Please in code questions give a [mcve]--cut & paste & runnable code & desired output & clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. Make your post self-contained. — philipxy, Apr 22 '19 at 03:09
"add a couple more tables to this query" does say what you want. Part of a [mcve] is a clear specification--a description of how the result you want is a function of the input. We only have an example so we can only guess what that is. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. When describing a result: Say enough that someone could go away & come back with a solution. When giving (base or query) relation(ship)s/associations or tables say what a row says about the business situation in terms of its column values when it is in the table. — philipxy, Apr 22 '19 at 07:22

score 1 · Accepted Answer · answered Apr 10 '14 at 02:20

You can just include table E with another left join to table D. Basically, the relation exists between tables D and E, and data entered into it has to be in accordance with it. If there is no data, the relation still exists, so the join will return nulls as you want.

 select
 TA.C1,TB.C1,TC.C1,TD.C1, TE.C1
 from  TableA TA          
 INNER JOIN TableB TB ON (TA.C2 = TB.C1)
 INNER JOIN TableC TC ON (TA.C3 = TC.C1)
 LEFT JOIN TableD TD ON (TA.C4 = TD.C1)
 LEFT JOIN TableE TE ON (TD.C2 = TE.C1)
 AND TA.C3 = "ABC"

score 0 · Answer 2 · answered Apr 10 '14 at 02:18

Best practice: Use explicit joins as you are in you later example.

When joining multiple tables the join need not all start with the same table, each one must simply be related. That is you can

select * 
  from            a 
       inner join b on a.id = b.id_a 
       inner join c on b.id = c.id_b

Oracle joining multiple tables?

2 Answers2