2 Removing oversize
source | link

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.

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.

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.

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.

1
source | link

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

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 |
+----+-----------+------------+

So my question is:

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.