从两个具有多重连接的不同表中选择两列[Mysql]

Question

我有四个表分别是users(id,name)、roles(id,name)、members(project_id,user_id,id)和member_roles(member_id, role_id)。那么如何同时从此表中选择 users.name 和 roles.name（project_id是准确选择我们需要的名称的条件）？我可以分别选择 users.name 和 roles.name，但是当我连接两个查询时，我的代码失败了。这是获取 users.name 的查询

select users.name from users
inner join members on members.user_id = users.id
where project_id = 1

这是获取 roles.name 的查询

select roles.name from roles
inner join member_roles on member_roles.role_id = roles.id
join members on members.id = member_roles.member_id
where project_id = 1 

Answer 1

你快到了。此外，为避免歧义，请在列 Name 上提供别名。

SELECT  DISTINCT 
        u.Name AS member_name,
        r.Name AS role_name
FROM    users u
        INNER JOIN members m 
            ON u.id = m.user_id
        INNER JOIN member_roles mr
            ON m.id = mr.member_id
        INNER JOIN roles r
            ON r.id = mr.role_id
WHERE   m.project_id = 1

Answer 2

你如何连接 2 个查询？像这样的事情怎么样：

select u.name as user_name, r.name as role_name from users u join members m on m.user_id = u.id join member_roles mr on mr.member_id = m.id join roles r on r.id = mr.role_id where m.project_id = 1;