LEFT JOIN 包含一个表中的组列表和另一个表中的标志

发布于 2024-11-07 13:11:53 字数 623 浏览 0 评论 0原文

首先，如果标题有点不对劲，请见谅。我将用一个例子来解释我的情况。

我有一个像这样的 Groups 表：

id  name
1   admin
2   developer
3   reviewer
4   beta tester
5   contributor

然后我有一个像这样的 Permissions 表：

id   user_id  group_id
1    60       1
2    60       2
3    60       3

我想要做的是使用 JOIN 查询最终得到如下结果：

(perm.) name   part of group
admin          1
developer      1
reviewer       1
beta tester    0
contributor    0

即，如果某个用户 ID 的条目链接到某个组 ID，则该用户位于该组中，因此将 1 放入该列中。我用它来打印管理页面的复选框列表。

我的问题很简单：在 MySQL 中如何做到这一点？显然是某种 JOIN，但它们让我很困惑。感谢您的任何帮助。

詹姆斯

原文

First off, sorry if the title is a little off. I'll explain my situation using an example.

I have a Groups table like this:

id  name
1   admin
2   developer
3   reviewer
4   beta tester
5   contributor

I then have a Permissions table like so:

id   user_id  group_id
1    60       1
2    60       2
3    60       3

What I want to do is use a JOIN query to end up with a result like this:

(perm.) name   part of group
admin          1
developer      1
reviewer       1
beta tester    0
contributor    0

I.e. If there is an entry for a certain user ID which links to a certain group ID, that user is in that group, so a 1 is put in the column. I'm using this to print out a list of checkboxes for an admin page.

My question is simply: how can this be done in MySQL? Obviously a JOIN of some kind, but they confuse the hell out of me. Thanks for any help.

James

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

自找没趣 2024-11-14 13:11:53

SELECT g.Name,
       CASE WHEN p.group_id IS NOT NULL THEN 1 ELSE 0 END AS part_of_group
    FROM Groups g
        LEFT JOIN Permissions p
            ON g.id = p.group_id
                AND p.user_id  = 60
    ORDER BY part_of_group DESC, g.Name

SELECT g.Name,
       CASE WHEN p.group_id IS NOT NULL THEN 1 ELSE 0 END AS part_of_group
    FROM Groups g
        LEFT JOIN Permissions p
            ON g.id = p.group_id
                AND p.user_id  = 60
    ORDER BY part_of_group DESC, g.Name

回复收藏 0 原文

永不分离 2024-11-14 13:11:53

SELECT
    Groups.name,
    Permissions.id is not null
FROM Permissions
RIGHT JOIN Groups ON Permissions.group_id = Groups.id 
WHERE Permissions.user_id = 60

RIGHT JOIN 意味着右表的每一行都有一行（即Groups）。如果左表中的相应行（即Permissions）不存在，则使用null 值创建它。

我希望我很清楚:)

Joe 提出的 LEFT JOIN 的答案也是完全正确的，但我认为如果您必须添加更多条件，这种方式更具可扩展性。

SELECT
    Groups.name,
    Permissions.id is not null
FROM Permissions
RIGHT JOIN Groups ON Permissions.group_id = Groups.id 
WHERE Permissions.user_id = 60

The RIGHT JOIN means you have a row for each row of the right table (ie Groups). If the corresponding row in the left table (ie Permissions) don't exists, it is created with null for values.

I hope I'm clear :)

The answer with a LEFT JOIN proposed by Joe is also perfectly correct, but I think this way is more extensible if you have to add more conditions.

回复收藏 0 原文

~没有更多了~