MySQL从一个表中选择所有行，即使使用中间表在其他表中不匹配 [英] mysql selecting all rows from one table even if no match in other using middle table

问题描述

我无法从表中选择所有行，并用0填充不匹配的行.

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

如果我运行用户名"vedran"的查询，我想得到这样的输出

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

因此，用户vedran的ID为1，而ID为1的用户具有活动的模块1和2，但我也想获取第三个模块，但将其设置为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.

模型如下所示:

我尝试使用此查询:

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'

编辑1 ，可以轻松完成CROSS JOIN经典解决方案ON 1 http: //sqlfiddle.com/#!2/30f01/2

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';

这篇关于MySQL从一个表中选择所有行，即使使用中间表在其他表中不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！