SQL多个包含连接

Question

下面是模式描述。我想构建一个查询，对于给定的用户，它将返回直接通过case_users或间接通过case_groups表共享的所有案例。这是我的尝试，我预先提取了用户所属的组：

SELECT * FROM `cases` 
INNER JOIN `case_users` ON `cases`.`id` = `case_users`.`case_id` 
INNER JOIN `case_groups` ON `cases`.`id` = `case_groups`.`case_id` 
WHERE `case_users`.`user_id` = '<USER_ID>' 
OR `case_groups`.`group_id` IN (<USER_GROUP_LIST>)

EXPLAIN返回以下内容：Impossible WHERE noticed after reading const table...

我怎样才能完成这件事？理想情况下，我希望在一次拍摄中检索所有案例，而不需要提取用户所属的USER_GROUP_LIST组。

mysql> describe users;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
+-------------+--------------+------+-----+---------+----------------+

mysql> describe cases;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
+-------------+--------------+------+-----+---------+----------------+

mysql> describe case_users;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| user_id     | int(11) | NO   | PRI | NULL    |       |
| case_id     | int(11) | NO   | PRI | NULL    |       |
+-------------+---------+------+-----+---------+-------+

mysql> describe case_groups;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| case_id     | int(11) | NO   | PRI | NULL    |       |
| group_id    | int(11) | NO   | PRI | NULL    |       |
+-------------+---------+------+-----+---------+-------+

mysql> describe group_users;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| group_id    | int(11) | NO   | PRI | NULL    |       |
| user_id     | int(11) | NO   | PRI | NULL    |       |
+-------------+---------+------+-----+---------+-------+

Answer 1

您的连接将只返回其Id同时位于case_users和case_groups中的案例。如果是其中之一，那么您需要两个查询，您可以联合这两个查询，以获得单个结果集中的所有结果：

SELECT `cases`.* FROM `cases` 
INNER JOIN `case_users` ON `cases`.`id` = `case_users`.`case_id` 
WHERE `case_users`.`user_id` = '<USER_ID>' 
UNION
SELECT `cases`.* FROM `cases` 
INNER JOIN `case_groups` ON `cases`.`id` = `case_groups`.`case_id` 
WHERE `case_groups`.`group_id` IN (SELECT `group_users`.`group_id`
                                   FROM `group_users`
                                   WHERE `group_users`.`user_id` = '<USER_ID>')