0

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.

  • 2
    Unclear. Please create a fiddle with sample data (showing in table form is not enough) and show desured result for that data. – Akina Nov 8 at 11:56
  • I'd ask for a fiddle as well - but I suggest you change from inner joins to (left) outer joins - since your foreign key columns contain NULL-values – eagle275 Nov 8 at 12:37
  • Why so many NULLs? – Rick James Nov 8 at 21:25
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.