MYSQL: Left join same table twice with same field

Question

I have a users table that stores admin and normal customers:

ID  Name    Role
1   John    Admin
2   Sara    User
3   Pete    User
4   Nick    User

Another Table that stores requests:

ID  Updatedby    Createdby DateCreated  DateUpdated
1   1              2        10/12/2017   21/01/2018
2   1              1        11/12/2017   22/01/2018
3   1              3        12/12/2017   23/01/2018
4   2              2        13/12/2017   24/01/2018

I am trying to create a view that looks like this:

ID  Updatedby    Createdby DateCreated  DateUpdated
1   John           Sara     10/12/2017   21/01/2018
2   John           John     11/12/2017   22/01/2018
3   John           Pete     12/12/2017   23/01/2018
4   Sara           Sara     13/12/2017   24/01/2018

The problem I am facing when creating the query is, I am trying to left join to the user table twice. How do I specify whether the Users.Name is for Updatedby or Createdby? This is what I mean:

SELECT
   request.ID,
   users.Name, /*name for Createdby */
   users.Name, /*name for Updatedby */
   requests.DateCreated,
   requests.DateUpdated
FROM
   requests
LEFT JOIN
   users ON requests.Updatedby = users.ID

I can't get my head around the logic with this.

You can join it twice and give it different aliases (join X as x1 on ..., X as X2 on ...) — tstenner, Apr 24 '18 at 17:13
you can try users.Name AS Createdby, users.Name AS Updatedby — Diego Avila, Apr 24 '18 at 17:14
Are you sure you want `LEFT JOIN` rather than `INNER JOIN`? Can you really have a `CreatedBy` that doesn't match a user? — Barmar, Apr 24 '18 at 17:15
Hi. Just *googing your title* or reading an intro to sql join to learn what it does tells you how to do this. Please see [ask] & [help]. PS This is a [tag:self-join]. https://stackoverflow.com/a/27682724/3404097 — philipxy, Apr 25 '18 at 07:23

score 1 · Accepted Answer · answered Apr 24 '18 at 17:18

this is an example:

SELECT toD.dom_url AS ToURL, 
    fromD.dom_url AS FromUrl, 
    rvw.*

FROM reviews AS rvw

LEFT JOIN domain AS toD 
    ON toD.Dom_ID = rvw.rev_dom_for

LEFT JOIN domain AS fromD 
    ON fromD.Dom_ID = rvw.rev_dom_from

you can use Alias

MYSQL: Left join same table twice with same field

1 Answers1