CODEWITHSUNDEEP
mysqljoin
jefflunt
jefflunt

Reputation: 33954

MySQL join - three tables, returning null where empty

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.

Upvotes: 1

Views: 997

Answers (2)

Bob Probst
Bob Probst

Reputation: 9641

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;

Upvotes: 1

Abhay
Abhay

Reputation: 6645

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.

Upvotes: 1

Related Questions