Join a table twice?

[jworisek]

This should be possible but I just can't think of it right now... 

 

WORK table (work_id, input_user_id, worker_user_id)

 

USERS table (user_id, user_name)

 

I need to get names from their respective IDs for both input_user_id (the person who inputs the job data) and worker_user_id (the person who does the job) in one sql statement.

 

So I currently have:

 

SELECT work_id, input_user_id, user_name as worker_name FROM work W INNER JOIN users U ON (W.worker_user_id=U.user_id)

 

And then do another query on input user_id.

[Wildbug]

SELECT
w1.work_id AS Job,
u1.username AS Work_by,
u2.username AS Input_by
FROM
work AS w1 JOIN users AS u1 ON w1.worker_user_id=u1.user_id,
work AS w2 JOIN users AS u2 ON w2.input_user_id=u2.user_id
WHERE
w1.work_id=w2.work_id;

[Wildbug]

You could even do it all in terms of JOINs and use a WHERE clause to SELECT a certain job (replacing '?' with a job ID).  I'm not sure which is the most efficient solution.

 

SELECT
w1.work_id AS Job,
u1.username AS Work_by,
u2.username AS Input_by
FROM
(work AS w1 JOIN users AS u1 ON w1.worker_user_id=u1.user_id) JOIN
(work AS w2 JOIN users AS u2 ON w2.input_user_id=u2.user_id) 
ON w1.work_id=w2.work_id
WHERE w1.work_id=?;

[bubblegum.anarchy]

To get all work recods with associated user records:

SELECT 
   work.work_id
   , input_user.*
   , worker_user.* 
FROM work
   INNER JOIN user AS input_user ON input_user_id = input_user.user_id
   INNER JOIN user AS worker_user ON worker_user_id = worker_user.user_id
;