NYCPHP Meetup

[Jake McGraw]

Ah, assuming you don't know the language prior to creating the query,
I think you'd use something like:

SELECT
	product.id
	, product.price
	, IF (lang_id IS NULL, 'en', lang_id) AS lang_id
FROM
	product
LEFT JOIN
	descriptions ON product.id = descriptions.product_id
WHERE
	product.category = :2

Notice that I removed ":1".

- jake

On Tue, Apr 22, 2008 at 1:31 PM, John Campbell <jcampbell1 at gmail.com> wrote:
> 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
>  _______________________________________________
>  New York PHP Community Talk Mailing List
>  http://lists.nyphp.org/mailman/listinfo/talk
>
>  NYPHPCon 2006 Presentations Online
>  http://www.nyphpcon.com
>
>  Show Your Participation in New York PHP
>  http://www.nyphp.org/show_participation.php
>