SQL多个外部联接(将t-sql联接转换为ANSI格式) [英] SQL multiple outer joins (converting t-sql joins to ANSI format)

问题描述

我有3个表t1，t2，t3.我想要基于以下条件的结果集:t1在t2上具有外部联接(t1的所有行)，t1在t3上具有外部联接(t1的所有行)，而t2在t3上具有外部联接(t2的所有行).如何在单个查询中使用这些外部3个联接? 基本上我想将t-sql格式的查询转换为ANSI格式.原始查询就是这样

I have 3 tables t1, t2, t3. I want a result set based on these conditions : t1 with outer join on t2 (all rows of t1), t1 with outer join on t3 (all rows of t1) and t2 with outer join on t3 (all rows of t2). How to use these outer 3 joins in a single query? Basically I want to convert a t-sql format query to ANSI format. The original query is something like this

Select * from t1, t2, t3
where t1.col1 *= t2.col1
  and t1.col2 *= t3.col1
  and t2.col2 *= t3.col2

我设法将前2个连接用作

I managed to use first 2 joins as

   Select * 
     from t1
left join t2 on t1.col1 = t2.col1
left join t3 on t1.col2 = t3.col1

这在前两个条件下正常工作.但是无法合并第3个联接.任何人都可以提出一种建议来做到这一点吗? 预先感谢.

This works properly for first 2 conditions. But wasn't able to incorporate the 3rd join. Can anyone please suggest a way to accompolish this? Thanks in advance.

推荐答案

从您的问题中，我想您只希望将t3中的行与t2中的与t1相连的行连接起来:

From your question I guess you only want rows from t3 that join with the rows from t2 that joined with t1:

SELECT 
    * 
FROM
    t1
    LEFT JOIN t2 ON t1.col1 = t2.col1
    LEFT JOIN t3 ON t1.col2 = t3.col1 AND t2.col2 = t3.col2

这将不包括来自t2和t3的行在col2上联接，除非来自t2的行已经与col1上的t1联接.

This will not include rows from t2 and t3 that join on col2, unless the rows from t2 have already joined with t1 on col1.

这篇关于SQL多个外部联接(将t-sql联接转换为ANSI格式)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！