左外连接与相同的表作为外连接的一部分

Question

我想知道如何通过编写外连接查询来获得所需的输出（如下所述） 其中外连接的表是其他连接条件在语句

给出的以下数据结构，其中 - 表A是含有一些任意对象 主表 - 表B由A引用其中A.TYPE_ID = B.ID - 表C之间defininfs关系表A中的C.SOURCE_ID参考A.ID和C.TARGET_ID参考A.ID

这是怎样的架构定义，我不能做任何事情（这是一个遗留系统）

TABLE_A     
--------------------------- 
| ID | TYPE_ID | Name | 
|-------------------------| 
| 1 | 1  | Name 1 | 
| 2 | 2  | Name 2 | 
| 3 | 1  | Name 3 | 
| 4 | 1  | Name 4 | 
| 5 | 3  | Name 5 | 
|-------------------------| 

TABLE_B 
---------------------- 
| ID | TYPE_NAME | 
|--------------------| 
| 1 | Type 1  | 
| 2 | Type 2  | 
| 3 | Type 3  | 
| 4 | Type 4  | 
|--------------------| 

TABLE_C 
------------------------------- 
| PK | SOURCE_ID | TARGET_ID | 
|-----------------------------| 
| 11 | 2   | 1   | 
| 12 | 2   | 3   | 
| 13 | 5   | 1   | 
| 13 | 5   | 4   | 
------------------------------- 

我想获得的“1型”表A中的所有对象是与它们与之相关联的对象的名称（否则为空），它们是类型2, 即外部连接以获得类型1的所有对象，而不管它们是否具有关联，但是如果它们这样做，那么我需要对象的名称。 请注意，类型1的对象将始终处于目标中。

对于上面例子中的输出是

------------------------------- 
| Target Name | Source Name | 
|-----------------------------| 
| Name 1  | Name 2  | 
| Name 3  | Name 2  | 
| Name 4  | (NULL)  | 
|-----------------------------| 

我原来的连接查询（不能得到外部联接工作），这是没有关联的正常连接不显示的对象。

select atrgt.NAME, asrc.NAME 
from TABLE_A atrgt 
JOIN TABLE_B trgttype on atrgt.TYPE_ID = trgttype.ID 
     and trgttype.TYPE_NAME = 'Type 1' 
JOIN TABLE_C assoc on atrgt.ID = assoc.TARGET_ID 
JOIN TABLE_A asrc  on asrc.ID = assoc.SOURCE_ID 
JOIN TABLE_B srctype on asrc.TYPE_ID = srctype.ID 
     and srctype.TYPE_NAME = 'Type 2' 

Answer 1

基本上在这种情况下我认为最好的办法是将查询细分为两个正常连接，然后做外的结果集之间的连接。如果您将SQL视为过程代码，您可能认为它看起来效率低下，但查询优化器不一定会独立运行这两个子连接。

你没有说你正在使用什么RDBMS。在Oracle我可能会写这样的：

with 
src_type_2 as (
    select c.target_id, a.name 
    from table_c c 
    join table_a on a.id = c.source_id 
    join table_b on b.id = a.type_id 
    where b.type_name = 'Type 2' 
), 
all_type_1 as (
    select a.id, a.name 
    from table_a a 
    join table_b on b.id = a.type_id 
    where b.type_name = 'Type 1' 
) 
select tgt.name, src.name 
    from all_type_1 tgt 
    left join src_type_2 src on src.target_id = tgt.id 

Answer 2

尝试

select atrgt.NAME, baseview.NAME 
from TABLE_A atrgt 
JOIN TABLE_B trgttype on atrgt.TYPE_ID = trgttype.ID 
     and trgttype.TYPE_NAME = 'Type 1' 
JOIN TABLE_C assoc on atrgt.ID = assoc.TARGET_ID 
LEFT JOIN ( 
    TABLE_A asrc  on asrc.ID = assoc.SOURCE_ID 
    JOIN TABLE_B srctype on asrc.TYPE_ID = srctype.ID 
     and srctype.TYPE_NAME = 'Type 2' 
) as baseview 

Answer 3

我认为这应该工作：

SELECT 
    TGT.NAME, SRC_TYPE.TYPE_NAME 
FROM TABLE_A TGT 
JOIN TABLE_B TGT_TYPE ON TGT.TYPE_ID = TGT_TYPE.ID 
LEFT JOIN TABLE_C REL ON TGT.ID = REL.TARGET_ID 
LEFT JOIN TABLE_A SRC ON REL.SOURCE_ID = SRC.ID 
LEFT JOIN TABLE_B SRC_TYPE ON SRC_TYPE.ID = SRC.TYPE_ID 
WHERE TGT_TYPE.TYPE_NAME = 'Type 1' AND COALESCE(SRC_TYPE.TYPE_NAME, 'Type 2') = 'Type 2' 

如果您正在使用Oracle，您可以用NVL(SRC_TYPE.TYPE_NAME, 'Type 2')更换COALESCE。