查询中有四个表表 a 包含 a_id , a_name表 a_tl 包含 a_tl_id 、 a_id 、 language_id 、 a_disp_name表 b 包含 b_id 、 a_id 、 b_name表 b_tl 包含 b_tl_id 、 b_id 、 language_id 、 b_disp_name
There are four table in the query Table a contains a_id , a_name Table a_tl contains a_tl_id , a_id , language_id , a_disp_name Table b contains b_id , a_id , b_name Table b_tl contains b_tl_id , b_id , language_id , b_disp_name
我想对 a 和 a_tl 进行左外连接，对 b 和 b_tl 进行左外连接以及对结果表的内部联接.我写了以下查询
I want to do a left outer join on a and a_tl , a leftouter join on b and b_tl and a inner join on the resultant 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此查询正在运行，因为数据库中存在 language_id，如果对于特定值不存在，它将不起作用，即左外连接不起作用
This query is working of the language_id is present in the database if for a particular value it is not present it will not work i.e. left outer join is not working
推荐答案看起来问题是，您没有使用 (+) 进行 language_id 检查.您的表是外连接的，因此 language_id 在未找到记录时为 NULL，但随后您检查 language_id = 2，但是?language_id 是 NULL.
Looks like the problem is, that you are not using (+) for your language_id checks. Your table is outer-joined, so language_id is NULL when no record is found, but then you check for language_id = 2, but ? language_id is NULL.
我也没有看到您在哪里使用 a_tl 或 b_tl 的结果，猜猜这只是您的帖子的问题，而不是您原始查询的问题?
I also don't see where you use results from a_tl or b_tl, guess that's just a problem of your post, not your original query?
但是，请使用显式连接而不是旧语法.一旦习惯了，阅读和理解就会容易很多.您的查询也可以从使用 COALESCE 中受益(或NVL，如果你喜欢):
However, please use explicit joins instead of the old syntax. Once you are used to it, it is a lot easier to read and understand. Your query could also benefit from using COALESCE (or NVL if you like):
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 )希望我答对了你的问题，如果它不起作用，请询问.
Hope I got your question right, please ask if it does not work.