如何构建一个命令来连接mySQL中的3个不同的表？

I have three different tables in MySQL:

我在MySQL中有三个不同的表:

given an user id, how can i get a list of role's name of this user? For example, user_id = 1 I need a list like this (1, deleteuser, modifyuser, viewuser)

给定用户ID,我如何获得该用户的角色名称列表?例如,user_id = 1我需要一个这样的列表(1,deleteuser,modifyuser,viewuser)

How can I construct my SQL command to get such a list?

如何构造我的SQL命令来获取这样的列表?

3 个解决方案

#1

JOIN the three tables like so:

像这样加入三个表:

SELECT 
  u.id,
  r.Name
FROM user u
INNER JOIN user_role ur ON u.id = ur.user_id
INNER JOIN roles r ON ur.Role_id = r.id
WHERE u.id = Someid

However, if you want the list of roles for each user to be concatenated into one string. Use GROUP_CONCAT like so:

但是,如果您希望将每个用户的角色列表连接成一个字符串。像这样使用GROUP_CONCAT:

SELECT 
  u.id,
  GROUP_CONCAT(r.Name) roles
FROM user u
INNER JOIN user_role ur ON u.id = ur.user_id
INNER JOIN role r ON ur.Role_id = r.id
WHERE u.id = 1
GROUP BY u.id

SQL Fiddle Demo

#2

SELECT User_id, Name
FROM User_role
JOIN Role ON User_role.Role_id = Role.Id
WHERE User_id = '1'

that would create a list like this:

这将创建一个这样的列表:

User_id | Name
1       | DeleteUser
1       | ModifyUser
1       | ViewUser

#3

Try this:

SELECT User_id, GROUP_CONCAT(r.Name)
FROM User_Role ur 
INNER JOIN Role r ON ur.Role_id = r.Role_id 
GROUP BY User_id;