CODEWITHSUNDEEP
androidsqlsqlite
ARLabs
ARLabs

Reputation: 1524

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

Upvotes: 0

Views: 58

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

In most dialects of SQL, you would solve this using row_number(), but you don't have that option. One method of solving this is with a correlated subquery -- this pulls out the first matching language for each str_id, which you can then use for filtering.

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_str_id = S2.str_id AND
        S1.lang_id = S2.lang_id
WHERE S1.lang_id IN ('it-it', 'it', 'en') AND
      S1.lang_id = (SELECT s3.LangId
                    FROM Strings s3
                    WHERE s3.str_id = S1.str_id
                    ORDER BY (CASE S3.lang_id
                                  WHEN 'it-it' THEN 1
                                  WHEN 'it' THEN 2
                                  WHEN 'en' THEN 3
                                  ELSE 4
                              END)
                   LIMIT 1
                  );

Upvotes: 1

DeadlyJesus
DeadlyJesus

Reputation: 1533

You could use the EXISTS operator to make sure that there is no other translation with a "better score": 

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")
AND NOT EXISTS (SELECT 1
                FROM STRINGS S3
                WHERE (CASE S3.lang_id
                      WHEN "it-it" THEN 1
                      WHEN "it" THEN 2
                      WHEN "en" THEN 3
                      ELSE 4 END) < (CASE S1.lang_id
                                     WHEN "it-it" THEN 1
                                     WHEN "it" THEN 2
                                     WHEN "en" THEN 3
                                     ELSE 4 END)

Upvotes: 0

Related Questions