Joining the Same Table Twice on Different Columns

Consider a scenario where you have a user table and a complaint table. The complaint table contains the user ID of both the person who opened the complaint and the person who closed it. The goal is to write a query that displays the usernames for both these individuals.

Initially, a query can retrieve the username for the person who opened the complaint:

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

However, to retrieve the username for the person who closed the complaint, you need to join the user table again using a different column. This is achieved using the following query:

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

In this query:

• Table A is used to join on the opened_by column.
• Table B is used to join on the closed_by column.

By joining the user table twice, you can retrieve the usernames for both the person who opened and closed the complaint, allowing you to display comprehensive information about each complaint.