MySQL Select Left Join multiple columns from the same table

Question

I have objects in the Main Project Workflow table that I need to relate to one object in the User table. The issue I am facing is when I do a LEFT JOIN, I can only relate one object at a time.

The relation I need to do is:

workflowUID = user_id
assignedTo   = user_id

I believe the problem is being caused by my LEFT JOIN, however, I don't know which join statement I need to use to do this relation.

User Table

user_id | user_firstName | user_lastName
1       | Joe            | Smith
2       | John           | Doe

Main Project Table

projectID | projectTitle | projectDesc   | projectDueDate | projectAssignedTo
1         | Test Title   | Desc for Proj | 11-06-2018     | 2

Main Project Workflow Table EDITED

projectID | CID | workflowUID | assignedTo
1         | 1   | 1           | 2

The projectID is releated to another table called mainProjects, which list more info about the project such as the project title, created/due date, created by, effort in hours, project description.

The CID is stored in the Main Project Workflow Table. It is the Commit ID. Which will be used later for stuff like editing/deleting comments.

Output

Workflow Created By | Workflow Assigned To
Joe Smith           | John Doe

SQL:

SELECT *
FROM mainprojectworkflow 
LEFT JOIN user ON mainprojectworkflow.workflowUID = user.user_id
WHERE projectID = $projectID
ORDER BY cid DESC

The second I try setting a second LEFT JOIN user ON mainprojectworkflow.workflowUID = user.user_id but instead, as a mainprojectworkflow.assignedTo I get a not unique table/alias user. I believe this is because I am already setting the user table to mainprojectworkflow.

EDIT I'm sorry, I should have been more clear on what's going on.

END RESULT: My plan is to use the SQL to select the data and display it in PHP on a website. It's a project management website. I want to be able to have PHP pull the variables from SQL so I can use them however I feel fit.

Did you consider modifying your table schema? because it was a pool design for Main Project Workflow table I think. — D-Shih
– D-Shih, Commented Nov 6, 2018 at 15:34
Can you explain what are projectID and cid or include these columns on the schema? — Shidersz
– Shidersz, Commented Nov 6, 2018 at 15:41
I updated the question to include information for the projectID and CID. I'm sorry, I should have included that. — tjwmagic
– tjwmagic, Commented Nov 6, 2018 at 16:19

Shidersz · Accepted Answer · 2018-11-06 15:48:30Z

3

You will need to join two times to the table user, like this:

SELECT
    mpw.workflowUID,
    CONCAT(cu.user_firstName, " ", cu.user_lastName) AS "Workflow Created By",
    mpw.assginedTo,
    CONCAT(au.user_firstName, " ", au.user_lastName) AS "Workflow Assigned To"
FROM
    mainprojectworkflow AS mpw
INNER JOIN
    user AS cu ON cu.user_id = mpw.workflowUID
INNER JOIN
    user AS au ON au.user_id = mpw.assignedTo
WHERE
    projectID = $projectID
ORDER BY
    cid DESC

answered Nov 6, 2018 at 15:48

Shidersz

17.2k2 gold badges28 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tjwmagic Over a year ago

Thank you! I made a minor change, from INNER JOIN to LEFT JOIN, then I added on a few others. I didn't realize my error was caused by the alias. After running your SQL code, I was able to quickly put it together. :)

JohnR · Accepted Answer · 2018-11-06 15:56:54Z

0

Try this type Of query :

SELECT 
CONCAT_WS(' ', uc.user_firstName, uc.user_lastName) AS Workflow_Created_By, 
CONCAT_WS(' ', ua.user_firstName, ua.user_lastName) AS Workflow_Assigned_To 
FROM mainprojectworkflow 
LEFT JOIN User uc ON mainprojectworkflow.workflowUID = uc.user_id 
LEFT JOIN User ua ON mainprojectworkflow.assignedTo = ua.user_id;

answered Nov 6, 2018 at 15:56

JohnR

572 silver badges8 bronze badges

Collectives™ on Stack Overflow

MySQL Select Left Join multiple columns from the same table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related