MySQL : Returning empty field instead of no row

Question

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!

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
– Glenn, Commented Apr 4, 2012 at 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
– Marcus Adams, Commented Apr 5, 2012 at 0:03

Ascherer · Accepted Answer · 2012-04-04 23:56:04Z

4

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

answered Apr 4, 2012 at 23:56

Ascherer

8,1213 gold badges45 silver badges60 bronze badges

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

1 Comment

Alec Sloman Over a year ago

Good god man. That took like... two minutes. Will accept the answer as soon as I can! Thanks so much.

nuvio · Accepted Answer · 2012-04-04 23:58:50Z

0

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

Just a suggestion...

answered Apr 4, 2012 at 23:58

nuvio

2,6254 gold badges34 silver badges62 bronze badges

Collectives™ on Stack Overflow

MySQL : Returning empty field instead of no row

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related