Stuck on multiple Left Join and Inner Join

1
votes

I've to make a query on Oracle and i'm a little bit stuck with it. In my TABLE1, I've 287 reccords so I want all informations from TABLE2 AND TABLE3 that egal with my 287 reccords (that's why I use Left Join). But I also want all reccords that match between TABLE2 and TABLE4, TABLE4 AND TABLE5 (That's why I use Inner Join).

But my query don't work and I don't know why. Someone can help me ?

My query : 

 SELECT distinct(TABLE1.NUM_SIN),      
       TABLE1.LIBELLE,
       TABLE1.DATE_FRAIS,
       TABLE2.CODE_SIN,
       TABLE2.PKPR,
       TABLE1.MT,
       TABLE4.POSTBUD,
       TABLE3.VEENG
  FROM TABLE1 
       LEFT JOIN TABLE2
                 ON TABLE2.NUM_SIN = TABLE1 .NUM_SIN
       INNER JOIN TABLE4
                 ON TABLE4.NUM_SIN = TABLE2.NUM_SIN 
                 AND TABLE4.SCSO = TABLE2.SCSO
       LEFT JOIN TABLE5
                 ON TABLE5.CDC = TABLE4.NO
                 AND TABLE5.CDEXE = TABLE4.CDEXE 
                 AND TABLE5.SCSO = TABLE4.SCSO
       LEFT JOIN TABLE3
                 ON TABLE3.CNCT = TABLE1.NUM_SIN  
 WHERE ... ;

A graph to understand : Graph Tables

Thx in advice !

2
sqloracleleft-joininner-join
What do you mean by your query doesn't work? Are you getting a fault message? If so, what is the message? Or, does the query you have run but it is not returning the data you expect? – Brian Driscoll
I don't see any values in your projection from TABLE5... are you using it in the WHERE clause (which you have not shown)? – Brian Driscoll
I don't show you all the query. There is some informations hidden. My real pblm is wih the lefjoin/innerjoin. I've the following error : " ORA-00904: "TABLE4"."CDEXE" : invalid identifier " – Drupal8ForTheWin
My Bad, In m select "..TABLE5.POSTBUD.." and not TABLE4. I rename it too fast. – Drupal8ForTheWin
Well the ora904 error is most likely to do with how the CDEXE column was created on TABLE4. If you used double quotes when you created the table (or altered to add the column), then you will have to use double quotes in your query as well. It could also be a casing issue, so you'll need to make sure that you're using the correct case when referencing the column. – Brian Driscoll

2 Answers

1
votes

I think the issue here is perhaps that you really don't want to use an inner join in your query, and perhaps that you don't know exactly what the difference is between an inner join and an outer join.

The inner join in your query will return ONLY the rows from TABLE4 that are a match in TABLE2. Joins are sequential and cumulative, so your remaining LEFT joins will have the reduced rowset on the left side of the join.

Thus, I believe you will want to use LEFT joins throughout your query, e.g.:

 SELECT distinct(TABLE1.NUM_SIN),      
       TABLE1.LIBELLE,
       TABLE1.DATE_FRAIS,
       TABLE2.CODE_SIN,
       TABLE2.PKPR,
       TABLE1.MT,
       TABLE4.POSTBUD,
       TABLE3.VEENG
  FROM TABLE1 
       LEFT JOIN TABLE2
                 ON TABLE2.NUM_SIN = TABLE1 .NUM_SIN
       LEFT JOIN TABLE4
                 ON TABLE4.NUM_SIN = TABLE2.NUM_SIN 
                 AND TABLE4.SCSO = TABLE2.SCSO
       LEFT JOIN TABLE5
                 ON TABLE5.CDC = TABLE4.NO
                 AND TABLE5.CDEXE = TABLE4.CDEXE 
                 AND TABLE5.SCSO = TABLE4.SCSO
       LEFT JOIN TABLE3
                 ON TABLE3.CNCT = TABLE1.NUM_SIN  
 WHERE ... ;
0
votes

Are you sure you don't want to left join to table4? The way it is written only values in TABLE4 would be allowed which would limit the results from table2 and table5.

NB - the image CDN is filtered here so I can't see the image.

SELECT --
FROM TABLE1 
LEFT JOIN TABLE2 ON TABLE2.NUM_SIN = TABLE1 .NUM_SIN
LEFT JOIN TABLE3 ON TABLE3.CNCT = TABLE1.NUM_SIN  

-- unless you want to reduce the number of table2 rows use left join here.
LEFT JOIN TABLE4 ON TABLE4.NUM_SIN = TABLE2.NUM_SIN AND TABLE4.SCSO = TABLE2.SCSO

LEFT JOIN TABLE5 ON TABLE5.CDC = TABLE4.NO
                AND TABLE5.CDEXE = TABLE4.CDEXE 
                AND TABLE5.SCSO = TABLE4.SCSO
WHERE ... ;