CODEWITHSUNDEEP
mysqlsql
AdrienXL
AdrienXL

Reputation: 3008

List all rows despite join

I don't understand why what i am trying doesn't work.

I have two tables

Table1
id_tb1    |  tb1field
1            aaa
2            bbb
3            ccc
4            ddd
5            eee

Table2
id_stuff  | id_tb1
1           1   
1           4
1           5
2           1
3           1

What id need is to join tb1 and tb2 but also list all records from tb1. Something like this :

Result
id_tb1    |  tb1field  | tb2.id_stuff
1            aaa         1
2            bbb         NULL
3            ccc         NULL
4            ddd         1
5            eee         1

So I did this : 

SELECT t1.*, t2.id_stuff
FROM Table1 t1
LEFT JOIN Table2 t2 ON t2.id_tb1 = t1.id_tb1
WHERE t2.id_stuff = 1

But obviously, it's not the good solution. Someone has an an idea ? Thx.

Upvotes: 1

Views: 36

Answers (2)

Vatsal Shah
Vatsal Shah

Reputation: 153

try this.

SELECT t1.*, t2.id_stuff
FROM Table1 t1
LEFT JOIN Table2 t2 ON t2.id_tb1 = t1.id_tb1 and t2.id_stuff = 1

Upvotes: 3

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

if you add a condition on a LEFT JOINed table in the WHERE clause, this is just as if you where doing an INNER JOIN.

Add that condition to the left join

SELECT t1.*, t2.id_stuff
FROM Table1 t1
LEFT JOIN Table2 t2 ON t2.id_tb1 = t1.id_tb1 and t2.id_stuff = 1

Upvotes: 3

Related Questions