Joining INTO many-to-many table (From Primary Key Table)

Question

This is how far i have gotten, though i don't think it can be done with 1 SQL-statement, i just want to confirm whether or not it is possible to do this with ONLY 1 statement:

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 table with foreignkey to users_mentors:

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

users_mentors table (in a many-to-many relationship with communes):

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

communes table (in a many-to-many relationship with 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 table (the m2m table that has FK to communes & users_mentors):

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

Is it possible to get all rows from users_mentors and a list of all their commune.type's, IF THEY EXIST (if mentor_geographies is empty, i want empty list of commune.type). In all cases i want the user.

Answer 1

If you want all users, use left join:

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;

I would also recommend that you use table aliases. They make the queries easier to write and to read:

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