mysql将两行合并为一行

Question

我有 2 个表，表名是 users 和 projects。表的结构是:

用户表

id | name   | role  
1  | samjad | user  
2  | saneer | constructor

项目表

id | name   | user_id | constructor_id |  
1  | school | 1       | 2              |  

如何根据项目表 ID 在一行中从两个表中获取所有详细信息。

我要选择

projectname username, constroctorname, user_id, constroctor_id 

在一行中

Answer 1

您可以加入用户表两次 - 一次作为用户，然后作为构造函数。

select p.name as projectname,
    u.name as username,
    c.name as contructorname,
    p.user_id,
    p.contructor_id
from projects p
left join user u on p.user_id = u.id
left join user c on p.contructor_id = c.id
where u.role = 'user'           -- check if the said user has role "user"
    and c.role = 'constructor'; -- check if the said constructor has role "constructor"