Reputation: 1
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
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