CODEWITHSUNDEEP
mysqlsqlleft-join
Andrew Sinagra
Andrew Sinagra

Reputation: 242

How to LEFT JOIN 3 tables ON multiple columns

I've been stuck on this one for a little bit. I have 3 tables and want to match tables 2 and 3 to table 1 on different columns.

tasks:
id      | item1_id       | item2_id   
--------------------------------------------
1       | 4              | 5   
2       | 5              | 6   
3       | 6              | 7  
--------------------------------------------

item1:
id      | item1_name     
--------------------------------------------
4       | item1_a              
5       | item1_b              
6       | item1_c              
--------------------------------------------

item2:
id      | item2_name     
--------------------------------------------
5       | item2_a              
6       | item2_b              
7       | item2_c              
--------------------------------------------

What I've been trying is:

SELECT tasks.id AS taskID, item1.name AS item1Name, item2.name AS item2Name
FROM tasks LEFT JOIN (item1 CROSS JOIN item2) 
ON (tasks.item1_id = item1.id AND tasks.item2_id = item2.id),
users, notes 
WHERE users.task_id = tasks.id
AND notes.task_id = tasks.id;

I'm returning tasks but not the info from item1 or item2.

Upvotes: 2

Views: 9006

Answers (3)

zessx
zessx

Reputation: 68790

Simply like that :

SELECT 
    tasks.is AS taskId,
    item1.item1_name AS item1Name,
    item2.item2_name AS item2Name
FROM tasks
JOIN users ON users.task_id = tasks.id
JOIN notes ON notes.task_id = tasks.id
LEFT JOIN item1 ON item1.id = tasks.item1_id
LEFT JOIN item2 ON item2.id = tasks.item2_id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Why are you doing a cross join? Just do two left joins:

SELECT tasks.id AS taskID, item1.name AS item1Name, item2.name AS item2Name
FROM tasks LEFT JOIN
     item1
     on tasks.item1_id = item1.id LEFT JOIN
     item2
     on tasks.item2_id = item2.id LEFT JOIN
     users
     on users.task_id = tasks.id LEFT JOIN
     notes
     on notes.task_id = tasks.id;

This will keep all records in tasks, along with all matching fields from the other tables.

Note: you shouldn't mix join criteria by using both on clauses and where clauses. In fact, the rule is simple. Eschew where clauses for joins. Always use on.

Upvotes: 6

user2615302
user2615302

Reputation: 194

try

    SELECT tasks.id AS taskID, item1.name AS item1Name, item2.name AS item2Name
    FROM tasks LEFT JOIN 
    ON tasks.item1_id = item1.id 
    LEFT JOIN item2 on tasks.item1_id = item2.id 
    JOIN users on users.task_id = tasks.id 
     JOIN notes on notes.task_id = tasks.id; 
    WHERE users.task_id = tasks.id
    AND notes.task_id = tasks.id;

Upvotes: 0

Related Questions