Mysql query to find first using IN clause with preference from left to right

Question

I have a test table with columns(id, title, language)

id: primary_key (auto-increment)

unique_key(composite) on language and title

id	title	language
1	Japanese	JP
2	Australian	AU
3	English	EN
4	Hindi	HI

I would like to have a query which either return 1 or 0 rows based on the multiple language criteria.

The query should return the result with language priority from left to right, if no row found for first language from left then look for the second language as so on.

Use-case:

languages	result row_id	remarks
JP,HI	1	As JP found at id=1
HI,JP	4	As HI found at id=4
RU,AU	2	As AU found at id=2, no row with language=RU
PK,ST	no row	As no language has value PK or ST

Here's I've tried custom order using FIELD clause on language column:

SELECT id, title, language
    FROM test WHERE language IN ('TH', 'AU','EN') ORDER BY 
FIELD(LANGUAGE,'TH','AU','EN') LIMIT 1;

Expected/Actual output:

id	title	language
2	Australian	AU

I would like to know is there any better way(in terms of performance and readability) to achieve this use-case?

Answer 1

Convert languages list to rowset:

SELECT test.id, test.title, language
FROM test 
JOIN (SELECT 'TH' language, 1 priority UNION ALL
      SELECT 'AU'         , 2          UNION ALL
      SELECT 'EN'         , 3          ) languages USING (language)
ORDER BY languages.priority LIMIT 1;

---

for 8+, the parameter format would be csv – Another coder

WITH RECURSIVE
parameter AS (
    SELECT @languages_as_CSV CSV
),
languages AS (
    SELECT SUBSTRING_INDEX(CSV, ',', 1) language, 
           SUBSTRING(CSV FROM 1 + LOCATE(',', CSV)) tail,
           1 priority
    FROM parameter
    UNION ALL
    SELECT SUBSTRING_INDEX(tail, ',', 1),
           SUBSTRING(tail FROM 1 + LOCATE(',', tail))
    FROM cte
    WHERE tail <> ''
)
SELECT test.id, test.title, language
FROM test 
JOIN languages USING (language)
ORDER BY languages.priority LIMIT 1;

I.e. recursive CTE parses CSV to separate values and adds priority data. Then the same query is executed, and parsed rowset is used instread of static languages list.

@languages_as_CS is a placeholder for a parameter which provides CSV languages list into the query.

Pay attention - none excess chars. 'TH,AU,EN' and not 'TH, AU, EN'.