Many to Many SQL Query

Question

I have three tables: Requests, Users and UserRequests.

Requests: - ID - Submit_ID - Assign_ID - Completed_ID

User: - ID - UserName

UserRequest: - ID - Request_ID - User_ID

Each request has a different user than the request was submitted by, assigned to and completed by. I'm trying for the following output:

Request.ID | Username of Assigned | Username of Submitted | Username of Completed

Am I going about this is the wrong way?

Answer 1

You can do what you are asking (multiple one-to-many joins) by giving the User table a different alias for each join as follows:

SELECT  
    Requests.ID AS `Request.ID`,  
    AssignUser.UserName AS `Username of Assigned`,  
    SubmitUser.UserName AS `Username of Submitted`,  
    CompleteUser.UserName AS `Username of Completed`  
FROM  
    Requests  
JOIN User AS AssignUser ON  
    Requests.Assign_ID = AssignUser.ID  
JOIN User AS SubmitUser ON  
    Requests.Submit_ID = SubmitUser.ID  
JOIN User AS CompleteUser ON  
    Requests.Completed_ID = CompleteUser.ID  

Note that the back ticks are used above for specifying the column aliases. If you aren't using MySQL, you would probably have to use double-quotes for Oracle (or a different character if you are using a different kind of database).

Answer 2

Didn't understand the purpose of UserRequest, but your intended output is obtained as follows:

SELECT
    r.ID,
    us.UserName,
    ua.UserName,
    uc.UserName
FROM
    Requests r
    JOIN Users us 
    ON r.Submit_ID = us.ID
    JOIN Users ua
    ON r.Assign_ID = ua.ID
    JOIN Users uc
    ON r.Complete_ID = uc.ID