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 <[EMAIL PROTECTED]> 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
>
_______________________________________________
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
