multple mysql join/merge query possible?

[DssTrainer]

I am trying to form a mysql statement that will look at 2 tables, "Product" and "Product Description" and depending on the language id that I pass, pull the corresponding language UNLESS there isn't one for that language, then I want to default to the english one

 

So looking at my example tables, you see I have 3 products. All 3 have english descriptions. But only 2 have spanish descriptions:

 

 

1. If I want to query all products where language_id = 1 I'd do:

select * from product p left join product_description pd on (p.product_id = pd.product_id) where pd.language_id = '1'

 

That will bring back 3 items matching the english language:

1 | x123 | Apple    | This is an apple

2 | y123 | Banana | This is a banana

3 | z123 | Orange  | This is an orange

 

2. If I want to query all products where language_id = 2 I'd do:

select * from product p left join product_description pd on (p.product_id = pd.product_id) where pd.language_id = '2'

 

That will bring back 2 items matching the spanish language:

1 | x123 | Manzana  | Esta es un manzana

2 | y123 | Plátano  | Esta es un Plátano

 

 

But instead of just bringing back 2 items, I want it to bring back any additional items that it doesn't have a language for, in english. So I'd like to see:

 

1 | x123 | Manzana  | Esta es un manzana

2 | y123 | Plátano  | Esta es un Plátano

3 | z123 | Orange  | This is an orange

 

Is there a way I can do it with multiple joins in a single sql statment to bring back the above?

 

Thanks

[corbin]

Hrmmm, no idea what the 'proper' solution would be, or even if there is one, but the easy solution would be to just always pull English.

 

 

select p.*,pd.*, pd2.name as name_eng, pd2.description as desc_eng from product p left join product_description pd on (p.product_id = pd.product_id AND pd.language = '1') LEFT JOIN product_description pd2 ON (pd2.product_id = p.product_id AND pd2.language = '2');

[DssTrainer]

nah.

 

The solution is a multipart join using mysql's COALESCE option to basically join the description table with one language to the description table of the other language and COALESCE will take the one that has a value.

 

But that single query takes more time, and is less flexible than a 2 simple mysql queries, one for each language, and then using php to merge the two.

 

So really, there is no real use for the super query.

[fenway]

It might be faster to determine the "dominant" language in a subquery for each....