SQL multiple inclusive joins

Question

Below is schema description. I would like to construct a query that for a given user will return all the cases that are shared directly via case_users OR indirectly via case_groups table. Here is my attempt, where I pull the groups the user belongs to upfront:

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 returns the following: Impossible WHERE noticed after reading const table...

How can I get it done? Ideally I would like to retrieve all the cases in a single shot - without pulling the USER_GROUP_LIST - groups that the user belongs to.

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

Your joins will only return cases whose Id is in both the case_users and case_groups.. If its one or the other, then you need 2 queries, which you can UNION to get all the results in a single resultset:

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>')