将这个组从MySQL查询中排除的正确方法是什么?

Table 1: Users

表1:用户

| profile_id | name    |
------------------------
| 1          | Joe     |
| 2          | Jane    |
| 3          | Jill    |
| 4          | Jeffery |

Table 2: User and Role to Team Lookup

表2:团队查找的用户和角色

| team_id | profile_id | role   |
---------------------------------
| 1       | 1          | coach  |
| 1       | 2          | player |
| 2       | 4          | coach  |
| 2       | 1          | player |

The scenario is that Jill is building a team, and the restriction is that you cannot be a player on more than one team. So I'm trying to build a query that pulls up those eligible to join Jill's team.

场景是Jill正在构建一个团队，限制是您不能在多个团队中作为一个参与者。因此，我正在尝试构建一个查询，将符合条件的人拉到Jill的团队中。

My first attempt was:

我的第一次尝试是:

SELECT `users`.`profile_id`
FROM `users` LEFT JOIN `user_role_to_team_lookup` AS `utr` USING(`profile_id`)
WHERE `utr`.`role` != 'player' OR `utr`.`role` IS NULL

The problem is that because Joe is a coach, he matches the criteria~ even though he is also already a player.

问题是，因为乔是一名教练，他符合标准——即使他已经是一名球员。

What would be the proper way to exclude users that are already players from the result set?

将已经是参与者的用户排除在结果集之外的正确方法是什么?

4 个解决方案

#1

I would write this without the subquery that most people use:

我写这篇文章时不会用到大多数人都会用到的子查询:

SELECT u.profile_id
FROM users AS u 
LEFT OUTER JOIN user_role_to_team_lookup AS utr 
  ON u.profile_id = utr.profile_id AND utr.role = 'player'
WHERE utr.profile_id IS NULL

In other words, look for a user who is already a player. Those who aren't a player will match no rows in the outer join, and therefore any column of utr will be NULL.

换句话说，寻找一个已经是玩家的用户。那些不是玩家的将不会匹配外部连接中的任何行，因此utr的任何列都是空的。

But it's best to put the condition in the ON clause of the join.

但是最好将条件放在join的ON子句中。

#2

SELECT u.profile_id
    FROM users u
    WHERE NOT EXISTS(SELECT 1
                         FROM user_role_to_team_lookup urtl
                         WHERE urtl.profile_id = u.profile_id
                             AND urtl.role = 'player')

#3

You can probably do this:

你可以这样做:

SELECT profile_id FROM users
WHERE profile_id NOT IN (SELECT DISTINCT profile_id FROM utr WHERE role = 'player');

#4

SELECT profile_id
FROM users
WHERE profile_id NOT IN (
    SELECT profile_id
    FROM user_role_to_team_lookup
    WHERE role = 'player');

#1