mysql - 返回表中的所有行并添加额外的列以指示数据透视表包含

Question

我正在尝试为带有一些分组复选框的表单创建数据，其中包括用户之前的选择。为了准备数据，我尝试返回复选框可见性表中的所有行，并添加一个额外的列，指示可见性表中的行是否出现在数据透视表 users_visibility 中。我该怎么做呢？

这是我所拥有的。它根据用户 ID 返回两个表的共同点。

select 
  visibility.id as visibility_id,
  visibility.title as visibility_title,
  users_visibility.users_id as checked
from visibility
left join users_visibility on users_visibility.visibility_id = visibility.id
where users_visibility.users_id = 2

表格示例

visibility
=======================
id  title
-----------------------
1   Drivers licence
2   No Criminal record
3   Senior volunteer

users_visibility
===========================
id  users_id  visibility_id
---------------------------
1   2         3

我想要退回的东西

========================================
visibility_id  visibility_title  checked 
----------------------------------------
1              Drivers licence    NULL
2              No Criminal record NULL
3              Senior volunteer   2 (user_id or whatever indicator)

谢谢。

Answer 1

对于左连接，除第一个表之外的所有表的条件都应位于on 子句中。

表别名还可以使查询更易于编写和阅读:

select v.id as visibility_id, v.title as visibility_title,
       uv.users_id as checked
from visibility v left join
     users_visibility uv
     on uv.visibility_id = v.id and uv.users_id = 2;