MySQL join - three tables, returning null where empty

Question

I've got the following schema:

phrase (in U.S. English):  translation:                                 code_value:
----------------------     -----------------------------------------    ------------------------------
| phrase_id | phrase |     | phrase_id | translation | language_cd |    | code class    | code value |
----------------------     -----------------------------------------    ------------------------------
|     1     | cheese |     |     1     |   fromage   |    FR       |    | LANGUAGE_CD   |    FR      |
----------------------     -----------------------------------------    | LANGUAGE_CD   |    DE      |
                                                                        | LANGUAGE_CD   |    ES      |
                                                                        ------------------------------

What this collection of data does is, for a given U.S. English phrase, it will give you the corresponding translation in three languages, French, German, and Spanish (at least within the context of our web app - we're not trying to be be Google Translation or anything).

What I'm trying to do is get a list of all translations for a given phrase, and if no translation into a given destination language exists, I want it to return NULL.

My query so far is:

SELECT phrase.phrase_id, phrase.string orig_phrase, code_value.code_value, translation.string as trans_phrase
FROM phrase, translation, code_value
WHERE code_value.code_class = 'LANGUAGE_CD' AND translation.phrase_id = phrase.phrase_id
ORDER BY orig_phrase;

Which returns:

-------------------------------------------------------
| phrase_id | orig_phrase | code_value | trans_phrase |
-------------------------------------------------------
|    1      | cheese      | FR         | fromage      |
|    1      | cheese      | DE         | fromage      |
|    1      | cheese      | ES         | fromage      |
-------------------------------------------------------

But what I intend for it to return is:

-------------------------------------------------------
| phrase_id | orig_phrase | code_value | trans_phrase |
-------------------------------------------------------
|    1      | cheese      | FR         | fromage      |
|    1      | cheese      | DE         | <NULL>       |
|    1      | cheese      | ES         | <NULL>       |
-------------------------------------------------------

I know I need a LEFT or RIGHT JOIN in there to get the NULL values back, but I can't seem to figure out exactly how to write it.

When this is all said and done, obviously we'll have the German and Spanish translations in there as well, and I need them to match up, but the purpose of the NULL values is to clearly show us into what languages we have yet to translate a given phrase.

Bob Probst · Accepted Answer · 2011-06-23 19:44:14Z

1

SELECT phrase.phrase_id, phrase.string orig_phrase, code_value.code_value, translation.string as trans_phrase
FROM phrase
inner join  translation on (translation.phrase_id = phrase.phrase_id)
left join code_value on (code_value.code_value=translation.language_cd)
WHERE code_value.code_class = 'LANGUAGE_CD' 
ORDER BY orig_phrase;

edited Jun 23, 2011 at 19:44

answered Jun 23, 2011 at 16:45

Bob Probst

9,7018 gold badges35 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Abhay Over a year ago

I haven't tested but looking at the query, I think that this will output NULL values for columns "phrase_id" and "orig_phase" for the non-matching language codes. So, for the example in the question it will output "1, cheese, FR, fromage", "NULL, NULL, DE, NULL", "NULL, NULL, ES, NULL"

Abhay Over a year ago

I'm afraid that a LEFT JOIN might as well be incorrect because then it will not show results at all for non-matching language codes. So DE and ES will not show at all.

Abhay · Accepted Answer · 2011-06-23 17:18:50Z

1

You are right that you'll need a LEFT or a RIGHT join. This should work:

SELECT `p`.`phrase_id`, `p`.`phrase` `orig_phrase`, `c`.`code_value`, `t`.`translation` `trans_phrase`
FROM `phrase` `p`
INNER JOIN (
    SELECT `code_value` 
    FROM `code_value` 
    WHERE `code_class` = 'LANGUAGE_CD'
) `c`
LEFT JOIN `translation` `t` ON `p`.`phrase_id` = `t`.`phrase_id` AND `c`.`code_value` = `t`.`language_cd`;

Hope this helps.

answered Jun 23, 2011 at 17:18

Abhay

6,6852 gold badges26 silver badges17 bronze badges

Collectives™ on Stack Overflow

MySQL join - three tables, returning null where empty

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related