MySQL select help

[Anon]

Hi,

This is my table:

TABLE PROVINCIAS
ID NAME PRIO LANGUAGE
1 Malaga 1 SPA
2 Gerona 1 SPA
2 Geronna 2 SPA
2 Girona 1 CAT

I want to select the whole list (the list of IDs) with a given language.

For example, giveProvincias("SPA") will return "1 Malaga" and "2 Gerona" (always PRIO=1).

But I want giveProvincias("CAT") return all the items in CAT language, and for the items with no CAT alternative, the SPA one instead.

For example: giveProvincias("CAT") will return "1 Malaga" and "2 Girona" (PRIO=1 in spa and cat languages).

I would try a subselect, but mysql doesn't support it yet.

Thanks a lot.

[Anon]

I didn't test this, but using a LEFT JOIN and isnull() should give you what you need:

select distinct t1.ID, isnull(t2.NAME, t1.NAME)
from PROVINCIAS t1
LEFT JOIN PROVINCIAS t2
ON t1.ID = t2.ID
AND t2.LANGUAGE = '$language'
AND t1.LANGUAGE = 'SPA'
WHERE t1.PRIO = 1

[Anon]

Thanks a lot 🙂

select distinct t1.pro_id_provincia, coalesce(t2.pro_nombre, t1.pro_nombre) as pro_nombre
from pv_provincias_variantes t1
LEFT JOIN pv_provincias_variantes t2
ON t1.pro_id_provincia = t2.pro_id_provincia
AND t2.idm_idioma_iso = '$language'
WHERE t1.pro_prioridad_nombre = 1
AND t1.idm_idioma_iso = 'SPA'