在有条件的多对多表上执行左联接 [英] Performing a left join across a many-to-many table with conditions

问题描述

一般情况

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

How do you perform a left join across a many-to-many relationship when you want to add a condition to the foreign side of the relation?

特定案例

我们正在处理两个表:team和pool.还有一个team_pool表用作它们之间的多对多连接表.另外，pool具有stage_id列.

We're dealing with two tables: team and pool. There is also a team_pool table serving as a many-to-many junction table between them. Additionally, a pool has a stage_id column.

我想在特定阶段检索该团队池中的所有团队.如果该池不存在，我希望该池为NULL.样本，理想化的结果:

I want to retrieve all teams with that team's pool for a specific stage. If the pool doesn't exist, I want the pool to be NULL. Sample, idealized results:

+--------+----------+------+
| 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.

If it's relevant, I'm using MySQL.

SQL

以下是用于创建表的(简化的)SQL:

Here's the (simplified) SQL used to create the tables:

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查询.

尝试的解决方案

• 在team至team_pool和team_pool至pool的范围内，使用INNER JOIN而不是LEFT JOIN.问题:我们会输掉不属于泳池的球队
• 使用WITH条件使pool上的stage_id与我们要查找的内容匹配，从team到team_pool以及team_pool到pool的左联接.问题:当一个团队属于多个资源池时，我们会得到多个结果.添加GROUP BY没有帮助.

• Use an INNER JOIN rather than a LEFT JOIN from team to team_pool and team_pool to pool. Issue: we lose teams that don't belong to a pool
• LEFT JOIN from team to team_pool and team_pool to pool using a WITH condition that the stage_id on pool matches what we're looking for. Issue: when a team belongs to many pools, we get multiple results. Adding a GROUP BY doesn't help.

选择解决方案

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

There are a lot of good solutions here.

鉴于无法实现理想的解决方案，我宁愿将stage_id添加到team_pool而不是使用UNION或子查询.这具有使我强制一个团队每个阶段只能属于一个池的附加好处.这也使查询变得简单:

Given that my ideal solution is not possible, I'd rather add stage_id to team_pool than use UNION or subqueries. This has the added benefit of letting me enforce that a team can only belong to one pool per stage. It also makes the queries simple:

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中的一列.阶段不是池的属性，而是团队到池的映射中的一个因素，对吧?

If I understand the concepts behind your schema, then I would think stage_id should be a column in team_pool rather than pool. The stage is not an attribute of the pool, it is a factor in the mapping of teams to pools, right?

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

Regardless, this is how I would write your query in Oracle. I'm not sure if this exact syntax is right for MySQL. Presumably you would want to parameterize the literal value for 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

这篇关于在有条件的多对多表上执行左联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！