如何在左连接分支内强制连接？

Question

我需要 LEFT 连接到整个 t2+t3 分支，但是如果我可以找到 t1 和 t2 之间的匹配连接，我想强制执行 t2 和 t3 连接。

   SELECT T1.name,T2.bob,T3.a
     FROM T1
LEFT JOIN T2 ON t1.id = t2.t1_id
     JOIN T3 ON t2.id = T3.t2_id

语法是什么？

样本数据：

T1 [id,name]
1 aaa
2 bbb
3 ccc

T2 [id,t1_id,bob]
1,1,777
2,1,888
2,2,999

T3[id,t2_id,a]
1,2,'yeh'

预期结果：

 [name] , [a]   , [bob]
  aaa   , 'yeh' , 888
  bbb   , NULL  , NULL
  ccc   , NULL  , NULL

Answer 1

编辑：此查询返回示例数据中预期的结果 -
（另请注意，此查询是在下面 Treefrog 的答案的帮助下进行的） -

SELECT t1.[name], t3.a, t2.bob
FROM T2 as t2
JOIN T3 as t3 ON t3.t2_id = t2.id
RIGHT JOIN T1 as t1 ON t1.id = t2.t1_id

我的旧答案 -

SELECT a
FROM T1 as t1
INNER JOIN T2 as t2 ON t1.id = t2.t1_id
LEFT JOIN T3 as t3 ON t2.id = t3.t2_id

Answer 2

SELECT T1.a
FROM T2
JOIN T3 ON T3.t2_id = T2.id
RIGHT JOIN T1 ON T1.id = T2.t1_id

Answer 3

这两个都会给你结果。不确定哪个会表现更好。

嵌套语句：

SELECT [name], NULL AS [a], NULL AS  [bob]
FROM t1
LEFT OUTER JOIN t2 ON t1.id = t2.t1_id
LEFT OUTER JOIN t3 ON t2.id = t3.t2_id 
WHERE t3.t2_id IS NULL
AND (SELECT COUNT(*) FROM t1 AS t1b LEFT OUTER JOIN t2 AS t2b ON t1b.id = t2b.t1_id LEFT OUTER JOIN t3 AS t3b ON t2b.id = t3b.t2_id WHERE t1.id = t1b.id AND t3b.a IS NOT NULL) = 0
UNION
SELECT [name], [a], [bob]
FROM t1
LEFT OUTER JOIN t2 ON t1.id = t2.t1_id
LEFT OUTER JOIN t3 ON t2.id = t3.t2_id 
WHERE t3.t2_id IS NOT NULL
ORDER BY t1.name

临时表：

CREATE TABLE #tmp_Rslt([name] varchar(50), [a] varchar(50), [bob] varchar(50))

--select matches
INSERT INTO #tmp_Rslt
SELECT [name], [a], [bob]
FROM t1
LEFT OUTER JOIN t2 ON t1.id = t2.t1_id
LEFT OUTER JOIN t3 ON t2.id = t3.t2_id 
WHERE t3.t2_id IS NOT NULL
ORDER BY t1.name

--select t1's that didn't have matches
INSERT INTO #tmp_Rslt
SELECT [name], NULL AS [a], NULL AS  [bob]
FROM t1
LEFT OUTER JOIN t2 ON t1.id = t2.t1_id
LEFT OUTER JOIN t3 ON t2.id = t3.t2_id 
WHERE t3.t2_id IS NULL
AND t1.[name] NOT IN (SELECT DISTINCT [name] FROM #tmp_Rslt)

SELECT *
FROM #tmp_Rslt

--cleanup.
DROP TABLE #tmp_Rslt

Answer 4

这应该适用于 MySQL

SELECT * FROM T1
LEFT JOIN (T2
 INNER JOIN T3 ON T2.id=T3.t2_id
) ON T1.id= T2.t1_id