CODEWITHSUNDEEP
mysqlleft-join
dtakis
dtakis

Reputation: 581

MySQL 2 left joins and no data returned

I use the following SQL to get a some keys I want to translate:

SELECT * FROM translate_keys 

LEFT JOIN translations 

ON translate_keys.id = translations.translate_key_id 

LEFT JOIN  languages 

ON languages.id = translations.language_id 

WHERE languages.is_default = 1;

If translations for a key is empty I get no results back (languages has content)! I understand that this is because languages get no relation with translations. What can I do? I thought that left join was enough!

Upvotes: 1

Views: 137

Answers (2)

sgeddes
sgeddes

Reputation: 62831

You are joining in the wrong direction. You want the Language table to be your master table. Try this:

SELECT * 
FROM languages  
  LEFT JOIN translations ON languages.id = translations.language_id 
  LEFT JOIN translate_keys ON translate_keys.id = translations.translate_key_id 
WHERE languages.is_default = 1;

Or if you're attempting to return all the keys and are receiving no results, it's because of your WHERE criteria -- as @Oswald correctly points out, move that to your JOIN and you should get records (obviously without translations or languages):

SELECT * 
FROM translate_keys 
   LEFT JOIN translations ON translate_keys.id = translations.translate_key_id 
   LEFT JOIN  languages ON languages.id = translations.language_id AND languages.is_default = 1;

Upvotes: 2

Oswald
Oswald

Reputation: 31637

The problem is the WHERE condition languages.is_default = 1. This eliminates all the dummy records added by the left join, because languages.is_default is NULL if a translation does not exist.

Upvotes: 1

Related Questions