jeudi 29 janvier 2015

SQL selection of "best" rows

I have a superficial knowledge of SQL and I'm in trouble with a query too complicated for me.


I have table of APPS with a column of name and description, that are ids to refer to the correct translation. And I have a table of localized Strings.


APPS (app_id, name_strid, description_strid) STRINGS (str_id, lang_id, strings)


I need a query that return all apps, with the best translation for each strings. best in a language order (let me say: it-it, it, en)


I reached a solution to get all apps order by languages:



SELECT A.app_id, S1.string, S2.string
FROM APPS as A
JOIN STRINGS AS S1
ON A.name_strid = S1.str_id
JOIN STRINGS AS S2
ON A.description_strid = S2.str_id
WHERE S1.lang_id = S2.lang_id
AND S1.lang_id IN ("it-it", "it", "en")
ORDER BY
CASE S1.lang_id
WHEN "it-it" THEN 1
WHEN "it" THEN 2
WHEN "en" THEN 3
ELSE 4
END;


How I can obtain only the best language?


Thanks


Aucun commentaire:

Enregistrer un commentaire