如果没有匹配，则使用默认值执行左连接。

Firstly, I'm not even sure if LEFT JOIN is the best way of doing what I want to do, so will try best to explain.

首先，我甚至不确定左加入是否是我想做的事情的最好方式，所以我会尽量解释。

I have 2 tables - one containing a list of sites, and another containing a list of roles. A user may just have one role, but they may have as many roles as there are sites.

我有两个表——一个包含站点列表，另一个包含角色列表。一个用户可能只有一个角色，但是他们可能有和网站一样多的角色。

Table 'sites'

表“网站”

site_id  |  site_name  | site_domain
---------------------------------------
1        |  Site A     | sitea.com
2        |  Site B     | siteb.com

Table 'roles'

表“角色”

site_id  |  user_id  | role_id
---------------------------------
1        |  1        | 1
1        |  2        | 1
2        |  2        | 2

As in my example above, User 1 has just one role on just one site, but User 2 has two roles on two different sites.

如我上面的示例所示，用户1在一个站点上只有一个角色，但是用户2在两个不同的站点上有两个角色。

What I'd like to do, and it's probably just a Sunday night not thinking straight kinda thing, is to get a list of sites out, along with the role for each individual user - if no role exists for that site for a user, then return NULL (or 0).

我想做的是，这可能只是一个周日晚上没有认真思考的事情，那就是拿出一个站点列表，以及每个用户的角色——如果一个用户的站点不存在任何角色，那么返回NULL(或0)。

Two examples of how the output should result:

输出结果的两个例子:

User 1

用户1

site_id | site_name | site_domain | role_id
----------------------------------------------
1       | Site A    | sitea.com   | 1
2       | Site B    | siteb.com   | NULL

User 2

用户2

site_id | site_name | site_domain | role_id
----------------------------------------------
1       | Site A    | sitea.com   | 1
2       | Site B    | siteb.com   | 2

The query I have at the moment is below, however, the 2nd site does not appear since the user doesn't have a role defined (ie. like above, but for User 1 the 2nd Site B will not list). I know I'm just missing something but cannot think of it tonight :o(

我现在的查询在下面，但是第二个站点没有出现，因为用户没有定义角色。就像上面一样，但是对于用户1，第二站点B将不会列出)。我知道我错过了什么，但今晚却想不起来了

SELECT site_id, site_name, site_domain, role_id 
FROM `sites` 
LEFT JOIN `roles` 
ON sites.site_id=roles.site_id 
WHERE roles.user_id='1'

1 个解决方案

#1

What you need to do is start with a table that combines all sites and users. Then join in the information in the roles table:

您需要做的是从一个包含所有站点和用户的表开始。然后加入角色表中的信息:

select s.site_id, s.site_name, s.site_domain, u.user_id, r.role_id
from (select distinct user_id from roles) u cross join
     sites s left outer join
     roles r
     on s.site_id = r.site_id and
        u.user_id = r.user_id

If you have a users table, then you can replace the first subquery with that table.

如果您有一个users表，那么您可以用该表替换第一个子查询。

For the sites where a single user is involved, you can just put a constant in the subquery:

对于涉及单个用户的站点，只需在子查询中输入一个常量:

select s.site_id, s.site_name, s.site_domain, u.user_id, r.role_id
from (select 1 as user_id) u cross join
     sites s left outer join
     roles r
     on s.site_id = r.site_id and
        u.user_id = r.user_id

Or a where clause: where u.user_id = 1.

或者where子句:where u。user_id = 1。

1 个解决方案

#1

更多相关文章

随机推荐