两个左外连接不起作用 Oracle sql [英] two left outer join not working Oracle sql

问题描述

查询中有四个表
表 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.

这篇关于两个左外连接不起作用 Oracle sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！