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

Issue

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?

Solution

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

Answered By – Akina

Answer Checked By – Clifford M. (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.