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

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?

CodePudding user response：

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;

CodePudding user response：

See if this will help:

SELECT ...
WHERE    FIND_IN_SET(language, languages)
ORDER BY FIND_IN_SET(language, languages)

FIND_IN_SET will return the empty set if there is no match, so something extra is needed to handle the "0" case.