MySQL一个表A中多个字段关联了表B的ID，如何关联查询？

表A：demo_user，用户表，记录用户信息

表B：demo_info，信息表，表中有提交人和确认人两个字段（其他字段暂不提及），都关联了用户表的用户ID

问题场景：想要在加载信息列表的时候，直接一次性关联查询到提交人和确认人的姓名。

SQL：

1. SELECT

2. demo_info.id,

3. user1.user_name AS commit_user_name,

4. user2.user_name AS ensure_user_name

5. FROM

6. demo_info

7. LEFT JOIN demo_user user1 ON demo_info.commituser_id = user1.user_id

8. LEFT JOIN demo_user user2 ON demo_info.ensureuser_id = user2.user_id

查询结果

总结：解决方法就是多次外连接查询同一张表，给表起不同的别名。

 

Position::select('*')->selectRaw('departmentst.department_name as a,departmentsto.department_name as b')
    ->join('departments as departmentst', 'departmentst.department_id', '=', 'Position.department')
    ->join('departments as departmentsto', 'departmentsto.department_id', '=', 'Position.department')
    ->get()
    ->toArray();