joining the same table twice on different columns

Question

I've got a user table and a complaint table.

The complaint table has the following structure:

[opened_by]   [complaint_text]   [closed_by]
 (user_id)         (text)         (user_id)
 (user_id)         (text)         (user_id)
 (user_id)         (text)         (user_id)

All users, both the complainers and complaint-resolvers are located in table user.

How do I write a query to show the username for both columns?

This gives me one:

SELECT user.username, complaint.complaint_text
FROM complaint
LEFT JOIN user ON user.user_id=complaint.opened_by

but I don't know how to write it so both _by columns show usernames rather than IDs.

Answer 1

SELECT 
     complaint.complaint_text, 
     A.username, 
     B.username
FROM 
     complaint 
     LEFT JOIN user A ON A.user_id=complaint.opened_by 
     LEFT JOIN user B ON B.user_id=complaint.closed_by

Answer 2

I prefer sub-queries as I find them easier to understand...

SELECT (SELECT name
            FROM user
            WHERE user_id = opened_by) AS opener,
       (SELECT name
            FROM user
            WHERE user_id = closed_by) AS closer,
       complaint_text
    FROM complaint;

Sub-queries are usually rewritten by the query optimiser, if you have any performance concerns.