如何两次联接到另一个表 - How to join twice times to another table?

How to join twice times to another table?

本文关键字：另一个何两次 | 更新日期: 2023-09-27

我得到了这个代码：

        SELECT
            topics.id,
            topics.id_first,
                            posts.id_first
          FROM topics
        LEFT
          JOIN posts
            ON posts.id = topics.id_first_msg

我的意图是做这样的事情：

        SELECT
            topics.id,
            topics.id_first,
                            posts.id_first,
                            posts.id_last
          FROM topics
        LEFT
          JOIN posts
            ON posts.id = topics.id_first_msg
        LEFT
          JOIN posts
            ON posts.id = topics.id_last_msg

但是，当我尝试两次执行左联接时，出现错误。那么哪种方式是正确的呢？谢谢。

您需要

为要加入的表提供多次别名：

SELECT
    topics.id,
    topics.id_first,
    p1.id_first,
    p2.id_last
FROM topics
LEFT JOIN posts p1 ON p1.id = topics.id_first_msg
LEFT JOIN posts p2 ON p2.id = topics.id_last_msg

您必须为第二个左连接设置别名;

    SELECT
        topics.id,
        topics.id_first,
                        posts.id_first,
                        posts2.id_last
      FROM topics
    LEFT
      JOIN posts
        ON posts.id = topics.id_first_msg
    LEFT
      JOIN posts AS posts2
        ON posts2.id = topics.id_last_msg

帖子表不明确。首先 - 你真的需要加入两次吗？你不能这样做吗：

    SELECT
        topics.id,
        topics.id_first,
        posts.id_first,
        posts.id_last,
        posts.id_first,
        posts.id_last
      FROM topics
    LEFT
      JOIN posts
        ON posts.id = topics.id_first_msg

其次 - 如果你真的必须加入两次 - 给帖子表两个不同的别名（你仍然需要编辑选定的列）