CODEWITHSUNDEEP
mysqljoin
Andres SK
Andres SK

Reputation: 10974

MySQL selecting all parents data with children data (including null values)

I have 3 tables:

My current app needs to retrieve the definitions of a requested word. Example:

Word caballo in spanish

SELECT word.id_word, word_item.id_item, word_item.definition  
FROM word 
INNER JOIN word_item ON word.id_word = word_item.id_word 
WHERE word.lang = "es" 
AND word.word LIKE "caballo" 
ORDER BY word_item.votes DESC, word_item.id_item

http://sqlfiddle.com/#!9/d0a44/4

This works great! But the app also needs to retrieve the definitions translations, so...

Word *caballo" in spanish with german translation

SELECT word.id_word, word_item.id_item, word_item.definition, word_item_translate.translation 
FROM word 
INNER JOIN word_item ON word.id_word = word_item.id_word 
LEFT JOIN word_item_translate 
ON word_item_translate.id_item = word_item.id_item 
AND (word_item_translate.id_item_translate OR word_item_translate.id_item_translate IS NULL) 
WHERE word.lang = "es" 
AND word.word LIKE "caballo" 
AND (word_item_translate.lang = "de" OR word_item_translate.lang IS NULL) 
ORDER BY word_item.votes DESC, word_item.id_item

http://sqlfiddle.com/#!9/d0a44/5

This doesn't work great. As you can see, the original word "caballo" has 11 different definitions inside word_item. Only 8 of those 11 records have been translated to german. Still, the second query should display the 11 records, and in the case of the 3 records that have no translation, the translation column should display a value of NULL.

Upvotes: 0

Views: 35

Answers (1)

Tomaso Albinoni
Tomaso Albinoni

Reputation: 1013

I think you're just confused about how to use LEFT JOIN. The following should work:

SELECT word.id_word, word_item.id_item, word_item.definition, word_item_translate.translation  
FROM word 
INNER JOIN word_item ON word.id_word = word_item.id_word
LEFT JOIN word_item_translate ON
word_item_translate.id_item = word_item.id_item
AND word_item_translate.lang = 'de'
WHERE word.lang = 'es' 
AND word.word LIKE 'caballo' 
ORDER BY word_item.votes DESC, word_item.id_item

Upvotes: 2

Related Questions