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?

*in terms of performance...* That kind of searching within a string isn't [sargable](https://en.wikipedia.org/wiki/Sargable). — SOS, Mar 15 '22 at 05:47

Akina · Accepted Answer · 2022-03-15T09:50:36.600

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'.

edited Mar 15 '22 at 09:50

answered Mar 14 '22 at 16:40

Akina

39,301
5
14
25

thanks for the solution, it also works fine. As the language criteria can be dynamic, so sub-query would vary based on the number of languages. Is it possible to handle the query in a dynamic way? – Another coder Mar 15 '22 at 03:22
@Anothercoder *Is it possible to handle the query in a dynamic way?* Of course. What is precise MySQL version? What is maximal (in theory in future) languages amount? – Akina Mar 15 '22 at 04:59
MySQL version is `5.7.30` but I will upgrade the version to `8.0.25` in near future. And maximal amount of languages would be around 8-10. – Another coder Mar 15 '22 at 05:09
@Anothercoder For 5.x the solution will contain static table with numbers 1-10... do you need in such solution really? or maybe the solution for 8+ is enough? And - what format of languages list provided into the query as a parameter is more convenient for you - CSV or JSON array? – Akina Mar 15 '22 at 05:12
For 5.x using static table, I can give a try by myself. And for 8+, the parameter format would be csv – Another coder Mar 15 '22 at 08:49

score 0 · Answer 2 · answered Mar 15 '22 at 03:55

0

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.

answered Mar 15 '22 at 03:55

Rick James

135,179
13
127
222

1

*FIND_IN_SET will return the **empty set** if there is no match* This is not correct. The function returns the searching string position in the strings list (as UNSIGNED INTEGER, it seems), and it returns **zero** value when the value to be searched for is not found. – Akina Mar 15 '22 at 09:59

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

2 Answers2