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.
Best Answer
http://sqlfiddle.com/#!9/33b19d/3 ...
as I said in the comments - use left join instead of inner join ...
your diagram misses the mentor_types ... so I commented it out.
of course you will get 2 lines for user 1886
For your question regarding all lines of user_mentors - no its impossible if you start building the select with the users table. And as there is no mentoruser_id that points to other lines than the id==4 line in users_mentors.