mysql - 连接 INTO 多对多表(从主键表)

Question

这就是我已经取得的进展，虽然我不认为可以用 1 个 SQL 语句来完成，我只是想确认是否可以只用 1 个语句来完成此操作:

SELECT * FROM users
INNER JOIN users_mentors ON users_mentors.id=users.mentoruser_id
INNER JOIN mentor_types ON (mentor_types.id=users_mentors.mentor_type OR users_mentors.mentor_type IS NULL)
INNER JOIN mentor_geographies ON mentor_geographies.mentor_id=users_mentors.id
INNER JOIN communes ON communes.id=mentor_geographies.commune_id
LIMIT 0,10

users 表，其外键为 users_mentors:

+------+---------+---------------+
| id   | user_id | mentoruser_id |
+------+---------+---------------+
| 1886 |    NULL |             4 |
| 1885 |    NULL |          NULL |
| 1884 |    NULL |          NULL |
| 1883 |    NULL |          NULL |
| 1882 |    NULL |          NULL |
+------+---------+---------------+

users_mentors 表(与communes 存在多对多关系):

+----+-------------+
| id | mentor_type |
+----+-------------+
|  4 |        NULL |
|  1 |           1 |
|  2 |           2 |
|  3 |           3 |
+----+-------------+

communes 表(与 users_mentors 存在多对多关系):

+----+--------------+-------+----------+
| id | name         | short | contract |
+----+--------------+-------+----------+
|  1 | København   | NULL  |        0 |
|  2 | Aarhus       | NULL  |        0 |
|  3 | Aalborg      | NULL  |        0 |
|  4 | Odense       | NULL  |        0 |
|  5 | Esbjerg      | NULL  |        0 |
+----+--------------+-------+----------+

mentor_geographies 表(具有 FK 到 communes 和 users_mentors 的 m2m 表):

+----+-----------+------------+
| id | mentor_id | commune_id |
+----+-----------+------------+
|  1 |         4 |          1 |
|  2 |         4 |          2 |
+----+-----------+------------+

是否可以从 users_mentors 获取所有行以及所有 commune.type 的列表(如果存在的话)(如果 mentor_geographies > 为空，我想要 commune.type 的空列表)。在所有情况下我都想要用户。

Answer 1

如果您想要所有用户，请使用左连接:

SELECT *
FROM users LEFT JOIN
     users_mentors
     ON users_mentors.id = users.mentoruser_id LEFT JOIN
     mentor_types
     ON mentor_types.id=users_mentors.mentor_type OR
        users_mentors.mentor_type IS NULL LEFT JOIN
     mentor_geographies
     ON mentor_geographies.mentor_id = users_mentors.id LEFT JOIN
    communes
    ON communes.id = mentor_geographies.commune_id
LIMIT 0, 10;

我还建议您使用表别名。它们使查询更容易编写和阅读:

SELECT *
FROM users u LEFT JOIN
     users_mentors um
     ON um.id = u.mentoruser_id LEFT JOIN
     mentor_types mt
     ON mt.id = um.mentor_type OR
        um.mentor_type IS NULL LEFT JOIN
     mentor_geographies mg
     ON mg.mentor_id = um.id LEFT JOIN
     communes c
     ON c.id = mg.commune_id
LIMIT 0, 10