根据条件在多对多表中执行左连接

一般案例

当您想要向关系的外部添加条件时，如何在多对多关系中执行左连接？

具体案例

我们正在处理两个表：team and pool。还有一个team_pool表充当它们之间的多对多联结表。另外，一个pool has a stage_id column.

我想检索特定阶段具有该团队池的所有团队。如果池不存在，我希望池为 NULL。理想化结果示例：

+--------+----------+------+
| team   | stage_id | pool |
+--------+----------+------+
| Team 1 |        2 | C    |
| Team 2 |     NULL | NULL | //this team doesn't belong to a pool for this stage (but it could belong to a pool for a different stage)
| Team 3 |        2 | G    |
| Team 3 |        2 | H    | //if a team belongs to a 2 pools for the same stage
| Team 4 |        2 | D    |

如果相关的话，我正在使用 MySQL。

SQL

以下是用于创建表的（简化的）SQL：

CREATE TABLE team (id BIGINT AUTO_INCREMENT, name VARCHAR(50), PRIMARY KEY(id));
CREATE TABLE team_pool (team_id BIGINT, pool_id BIGINT, PRIMARY KEY(team_id, pool_id));
CREATE TABLE pool (id BIGINT AUTO_INCREMENT, stage_id BIGINT, name VARCHAR(40) NOT NULL, PRIMARY KEY(id));

理想的解决方案

理想的解决方案是：

• 不需要更改我的架构（ORM 确实希望这样）
• 需要单个非 UNION 查询。

尝试过的解决方案

• 使用 INNER JOIN 而不是 LEFT JOINteam to team_pool and team_pool to pool。问题：我们失去了不属于某个池的球队
• 左连接自team to team_pool and team_pool to pool使用WITH条件stage_id on pool符合我们正在寻找的内容。问题：当一个团队属于多个池时，我们会得到多个结果。添加 GROUP BY 没有帮助。

编辑：选择的解决方案

这里有很多好的解决方案。

鉴于我的理想解决方案是不可能的，我宁愿添加stage_id到 team_pool 而不是使用 UNION 或子查询。这还有一个额外的好处，那就是让我强制执行每个阶段一个团队只能属于一个池。它还使查询变得简单：

SELECT t.name, p.name, tp.stage_id FROM team t LEFT JOIN team_pool tp ON t.id = tp.team_id AND tp.stage_id = 2 LEFT JOIN pool p ON p.id = tp.pool_id

如果我理解你的模式背后的概念，那么我会想stage_id应该是一列team_pool而不是pool。阶段不是池子的属性，而是团队到池子映射的一个因素，对吗？

无论如何，这就是我在 Oracle 中编写查询的方式。我不确定这个确切的语法是否适合 MySQL。大概您想要参数化以下内容的文字值：stage_id.

SELECT t.name, p.name
  FROM (SELECT team.name, pool_id
          FROM team LEFT JOIN team_pool
            ON team_pool.team_id = team.team_id ) t
       LEFT JOIN (SELECT pool_id, name FROM pool WHERE stage_id = 2) p
            ON p.pool_id = t.pool_id