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 t

Solution 1:

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.stringFROM 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
                    ORDERBY (CASE S3.lang_id
                                  WHEN'it-it' THEN 1WHEN'it' THEN 2WHEN'en' THEN 3ELSE4END)
                   LIMIT 1
                  );

Solution 2:

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.stringFROM 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")
ANDNOT EXISTS (SELECT1FROM STRINGS S3
                WHERE (CASE S3.lang_id
                      WHEN"it-it"THEN1WHEN"it"THEN2WHEN"en"THEN3ELSE4END) < (CASE S1.lang_id
                                     WHEN"it-it"THEN1WHEN"it"THEN2WHEN"en"THEN3ELSE4END)