CODEWITHSUNDEEP
mysql
ash
ash

Reputation: 1

MySQL Join needs to show all records from LEFT table

I am wondering if someone can help.

Please see below:

SELECT id,name FROM words;

id , name

1 , Still

2 , Sparkling

3 , Fizzy

SELECT * from translation;

words_id, lang_id,translation

1, 1, AStill

1, 2, BStill

2, 1, ASparkling

I needed result like below:

id , name , lang_id, translation

1,Still, 1, AStill

2, Sparkling, 1, ASparkling

3, Fizzy, NULL , NULL

The query i have tried

SELECT id,name,lang_id,translation FROM words LEFT JOIN translation ON words_id=id AND lang_id=1;

http://sqlfiddle.com/#!9/f544b/3

Many thanks

Upvotes: 0

Views: 1129

Answers (1)

Eric
Eric

Reputation: 3257

Your condition lang_id=1 turns your LEFT JOIN into INNER JOIN. The reason is when the condition is not satisfied (ie no entry in translation table), it will not display it. If you want to display everything on the left table, remove the condition lang_id=1.

SELECT id,name,lang_id,translation 
FROM words 
LEFT JOIN translation ON words_id=id

If you need the condition, you can put it in sub-query.

SELECT id,name,lang_id,translation 
FROM words w
LEFT JOIN (
    SELECT * 
    FROM translation
    WHERE lang_id = 1
) t ON t.word_id = w.id

Upvotes: 1

Related Questions