SQL多重外部连接(将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

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

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

I managed to use first 2 joins as

我成功地使用了前2个join as

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

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.

这适用于前两个条件。但是第三次加入失败了。请问有什么办法可以解决这个问题?提前谢谢。

2 个解决方案

#1

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

从你的问题中，我猜你只想要t3的行与t2的行相连接与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

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.

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

#2

You can have several variations, all with different result sets. Which of them was the original intention is difficult if not impossible for others to tell:

您可以有几个变体，所有都有不同的结果集。其中哪一个是最初的意图，即使不是不可能，也很难让别人知道:

(Variation 1 - Tobsey's query, join first to t2, then to t3, equivalent to):

(变体1 - Tobsey的查询，先连接到t2，再连接到t3，等效于):

SELECT 
    * 
FROM
        t1
    INNER JOIN                                --- or LEFT JOIN
        t2 
            ON  t1.col1 = t2.col1
    LEFT JOIN 
        t3 
            ON  t1.col2 = t3.col1 
            AND t2.col2 = t3.col2     --- this line makes the first LEFT join
                                      --- equal to INNER join

(Variation 2 - join first to t3, then to t2 ):

(变异2 -先连接到t3，再连接到t2):

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

(Variation 3a - join first t2 to t3, then join t1 to that join ):

(变体3a -加入第t2到t3，然后加入t1到该连接):

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

Variation 3 can have several more sub-variations if you replace the first or the second LEFT join with an INNER join.

如果您将第一个或第二个左连接替换为内连接，则变体3可以有更多的子变体。

My guess is you want variation 3a.

我猜你想要变型。

#1