MySQL : Returning empty field instead of no row

I have two tables, one for English category names, and another for Spanish category names.

I am attempting to write a small interface which allows an admin to see for which English categories a translation must still be provided.

I will write it assuming that when you create a Spanish category, it inherits the ID of the English category.

To get a results set of corresponding categories, I have this query:

SELECT tbl_cms_categories_en.id as id, 
   tbl_cms_categories_en.name as en, 
   tbl_cms_categories_es.name as es 
FROM   tbl_cms_categories_en, tbl_cms_categories_es 
WHERE  tbl_cms_categories_en.id = tbl_cms_categories_es.id

This returns a nice list of entries:

enter image description here

This serves my purpose well, but there is one deficiency. If there is no row in the Spanish table, it does not return a derived row at all.

How could I change my query so that, if there is a row in the English table but not the Spanish one, I could return the derived row to say "No translation found".

Akin to:

ID = 8, en = "Security", es = "Translation Not Found"

Any guidance would be bigly appreciated.

Thanks dudes!

mysql

2012-04-04 23:53
by Alec Sloman

You'll want to do a quick read on OUTER JOIN. Then use COALESCE or the equivalent to replace your null field with your "not found" string. And consider the other way around (Spanish but no English) - Glenn 2012-04-04 23:57

You'll need to use the explicit JOIN syntax with a LEFT OUTER JOIN, using JOIN and ON clauses, since an implicit join is always an inner join - Marcus Adams 2012-04-05 00:03

try

SELECT tbl_cms_categories_en.id as id, 
   en.name as en_name, 
   IF(es.name IS NULL, "No Translation Found", es.name) as es_name 
FROM   tbl_cms_categories_en AS en
LEFT JOIN tbl_cms_categories_es AS es
    ON tbl_cms_categories_en.id = tbl_cms_categories_es.id

2012-04-04 23:56
by Ascherer

Good god man. That took like... two minutes. Will accept the answer as soon as I can! Thanks so much - Alec Sloman 2012-04-04 23:56

made a quick edit there, should work, and quicken the logi - Ascherer 2012-04-04 23:58

Glad to help : - Ascherer 2012-04-04 23:59

Why don't you set up a default value for all the Spanish rows to "Translation Not Found"?

Just a suggestion...

2012-04-04 23:58
by nuvio

Spanish rows may not exist is the point - Alec Sloman 2012-04-05 00:01