NYCPHP Meetup

[John Campbell]

I am a bit stumped on this one.

I have a products table with a standard auto number primary key, and a
descriptions table that is keyed off the product id and a language id
('en','es','zh_cn', etc)

I want to join the description table to the product table on a 1:0,1
basis, and if the users language is something other than english, I
want to use that language as the default but fall back on english.

I could do something like:

SELECT product.id, product.price, (SELECT d.description FROM
descriptions d WHERE d.product_id=product.id AND d.lang_id IN
(:1,'en') ORDER BY d.lang_id!='en' DESC LIMIT 0,1) as description
FROM product
WHERE product.category=:2

It works, but it sucks because I then don't know the language of the
result.  Of course I could add another sub select, but I feel like I
am missing some really simple way to just join the tables according to
the required condition.

Any help here would be appreciated.

Regards,
John Campbell