Mysql get result from same table with two id columns-CodePudding

I have two tables:

select * From user;
 --------- --------- ----------- ------------- 
| user_id | role_id | name      | city        |
 --------- --------- ----------- ------------- 
|       1 |       1 | aaa       | NULL        |
|       2 |       6 | bbb       | NULL        |
|       3 |       6 | ccc       | NULL        |
|       4 |       2 | ddd       | NULL        |
|       5 |       3 | eee       | NULL        |
|      10 |       6 | fff       | NULL        |
|      22 |       3 | ggg       | NULL        |
 --------- --------- ----------- ------------- 

select * From user_log;
 -------- --------- ------------------------ --------------------- ------------ 
| log_id | user_id | action                 | changed_date        | updated_by |
 -------- --------- ------------------------ --------------------- ------------ 
|      1 |       4 | Changed ddd's city     | 2022-06-26 07:59:23 |          1 |
|      2 |      22 | Changed ggg's name     | 2022-06-26 08:08:06 |          5 |
|      3 |      10 | Changed fff's name     | 2022-06-26 10:19:26 |          4 |
|      4 |      10 | Changed fff's city     | 2022-06-26 10:19:26 |          4 |
|      6 |      10 | Changed fff's city     | 2022-06-26 10:20:30 |          1 |
 -------- --------- ------------------------ --------------------- ------------

Look at the user_id and updated_by columns in user_log table. For those two columns, I use same user_id from user table.

Now I need to get a result with both user's name which are related to those two ids.

This is the result I need.

 ------------- --------------- --------------------- --------------------- ----------------- ------------------- 
| log_user_id | log_user_name | action              | changed_date        | updated_user_id | updated_user_name |
 ------------- --------------- --------------------- --------------------- ----------------- ------------------- 
|           4 | ddd           | Changed ddd's city  | 2022-06-26 07:59:23 |               1 | aaa               |
|          22 | ggg           | Changed ggg's name  | 2022-06-26 08:08:06 |               5 | eee               |
|          10 | fff           | Changed fff's name  | 2022-06-26 10:19:26 |               4 | ddd               |
|          10 | fff           | Changed fff's city  | 2022-06-26 10:19:26 |               4 | ddd               |
|          10 | fff           | Changed fff's city  | 2022-06-26 10:20:30 |               1 | aaa               |
 ------------- --------------- --------------------- --------------------- ----------------- -------------------

I tried it something like this. But it gives same name for both name columns.

SELECT u.user_id as log_user_id
     , u.name as log_user_name 
     , action
     , changed_date
     , u.updated_by as updated_user_id 
     , updated_user_name 
FROM user u 
  JOIN (
    SELECT u.user_id
         , u.name as updated_user_name
         , action
         , DATE_FORMAT(l.changed_date,'%Y-%m-%d') as changed_date
    FROM user u 
      JOIN user_log l ON l.updated_by = u.user_id
  ) s USING (user_id)

Hope somebody may help me to figure this out.

CodePudding user response：

It would appear you just need to join the user table twice, once for each User_Id

select l.user_id as log_user_id,
  lu.name as log_user_name, 
  l.action, 
  date_format(l.changed_date,'%Y-%m-%d') as changed_date,
  l.updated_by as updated_user_id, 
  uu.name as updated_user_name
from user_log l
join user lu on lu.user_id = l.log_user_id
join user uu on uu.user_id = l.updated_by;