Two left outer joins do not work Oracle sql

Question

There are four tables in the query. Table a contains a_id , a_name
The a_tl table contains a_tl_id , a_id, language_id, a_disp_name
Table b contains b_id , a_id, b_name
The b_tl table contains b_tl_id , b_id, language_id, b_disp_name

I want to make a left outer join on a and a_tl, a leftouter join on b and b_tl
and inner join in result tables. I wrote the following query

SELECT case a.a_disp_name WHEN null THEN a.a_name else a.a_disp_name end AS a_name , case b.b_disp_name WHEN null THEN b.b_name else b.b_disp_name end AS b_name , a_id , b_id FROM a , a_tl , b , b_tl WHERE a.a_id = a_tl.a_id (+) AND b.b_id = b_tl.b_id (+) AND a_tl.language_id = 2 AND b_tl.language_id = 2 AND a.a_id= b.b_id 

This request works with language_id, which is present in the database, if for a certain value it is absent, it will not work, i.e. left outer join does not work.

Answer 1

It seems that the problem is that you are not using (+) for your language_id checks.
Your table is joined externally, so language_id is NULL when the record is not found, but then you check language_id = 2 , but? language_id - NULL .

I also don’t see where you use the results from a_tl or b_tl , guess that this is just the problem of your message and not your original request?

---

However, instead of explicit syntax, use explicit joins. Once you are used to it, it is much easier to read and understand. Your request may also benefit from using COALESCE (or NVL if you want):

 SELECT COALESCE( a_tl.a_disp_name, a.a_name ) AS a_name, COALESCE( b_tl.b_disp_name, b.b_name ) AS b_name, a.a_id, b.b_id FROM a JOIN b ON ( b.b_id = a.a_id ) LEFT JOIN a_tl ON ( a_tl.a_id = a.a_id AND a_tl.language_id = 2 ) LEFT JOIN b_tl ON ( b_tl.b_id = b.b_id AND b_tl.language_id = 2 ) 

I hope I answered your question correctly, please ask if it works.

Answer 2

If you want a_tl.language_id = 2 , you force an inner join (a_tl.language_id will never be null).

If you want to leave the connection only with strings from a_tl with a_tl.language_id = 2, write like this:

 FROM a join b on (a.a_id= b.b_id) left join a_tl on ( a.a_id = a_tl.a_id and a_tl.language_id = 2) left join b_tl on ( b.b_id = b_tl.b_id and b_tl.language_id = 2 )