Mysql selecting all the rows of a table even if no correspondence in another intermediate table

advertisements

I am having trouble with selecting all rows from a table and populating non matching ones with 0.

Table "modul"
----------------------------------------------------------
| id    | menu_name         | modul_name    | all_access |
----------------------------------------------------------
    1       books               book            0
    2       cds                 cd              0
    3       tapes               tape            0
table "user"
--------------------
| id    | username |
--------------------
    1       vedran
table "user_modul"
------------------------------------------
| id    | user_id   | modul_id  | active |
------------------------------------------
    1       1           1           1
    2       1           2           1
    3       2           2           1

If I run the query for username "vedran" I'd like to get an output like this

------------------------------------------------------------------
| id    | menu_name     | modul_name    | all_access    | active |
------------------------------------------------------------------
 1          books           book            0               1
 2          cds             cd              0               1
 3          tapes           tape            0               0

So user vedran has id 1 and user with id 1 has active modules 1 and 2 but I'd like to get third module as well but set to 0.

Model looks like this:

I tried using this query:

SELECT M.*, ZM.active FROM modul M JOIN user_modul UM ON (M.id = UM.modul_id) JOIN user U ON (UM.user_id = U.id) WHERE U.id = 1 ORDER BY M.id ASC

But that returns just the two rows and not all the rows from "modul" table.


http://sqlfiddle.com/#!2/30f01/1

SELECT
  m.* , COALESCE(um.active,0)
FROM user u
RIGHT JOIN modul m
ON 1
LEFT JOIN user_modul um
ON um.modul_id = m.id AND um.user_id=u.id
WHERE u.username='vedran'

EDIT 1 CROSS JOIN classic solution without tricky ON 1 http://sqlfiddle.com/#!2/30f01/2

SELECT
  m.* , COALESCE(um.active,0)
FROM user u
CROSS JOIN modul m
LEFT JOIN user_modul um
ON um.modul_id = m.id AND um.user_id=u.id
WHERE u.username='vedran';