CODEWITHSUNDEEP
mysqlsqljoin
Mike Oram
Mike Oram

Reputation: 765

SELECT different users from same table in single JOIN

I am currently trying to join a table (tasks) with another table (users). I need to select different rows from the users table based on values in the tasks table:

tasks(user1, user2, user3);

users(id, name, email);

Previously I have linked data using a LEFT JOIN like so:

SELECT * FROM tasks LEFT JOIN users ON tasks.user1 = users.id;

Thats pretty simple, so I have tried using an AND like so:

SELECT * FROM tasks LEFT JOIN users ON tasks.user1 = users.id AND tasks.user2 = users.id;

but that returns the users fields as NULL, which makes sense as tasks.user1 is different to tasks.user2

I am pretty stumped on how to do this now, all suggestions welcome!

Upvotes: 1

Views: 1485

Answers (2)

Christos
Christos

Reputation: 53958

use this one:

SELECT * 
FROM tasks 
LEFT JOIN users 
ON tasks.user1 = users.id 
OR tasks.user2 = users.id;

Upvotes: 1

StuartLC
StuartLC

Reputation: 107237

You'll need to repeat the join and alias the users table diffeently for each joined column:

SELECT * FROM tasks 
LEFT JOIN users u1 ON tasks.user1 = u1.id 
LEFT JOIN users u2 ON tasks.user2 = u2.id 
...

Upvotes: 2

Related Questions