MYSQL join, return first matching row only from where join condition using OR

Question

Hello everyone,

I'm having a problem with a particular MySQL query.

I have table1, and table2, table2 is joined onto table1.

Now the problem is that I am joining table2 to table1 with a condition that looks like:

SELECT
table1.*, table2.*
JOIN table2 ON (     table2.table1_id = table1.id
                 AND (    table2.lang = 'fr'
                       OR table2.lang = 'eu'
                       OR table2.lang = 'default') )

I need it to return only 1 row from table2, even though there might exists many table2 rows for the correct table1.id, with many different locales.

I am looking for a way to join only ONE row with a priority of the locales, first check for one where lang = something, then if that doesn't manage to join/return anything, then where lang = somethingelse, and lastly lang = default.

FR, EU can be different for many users, and rows in the database might exist for many different locales.. I need to select the most suitable ones with the correct fallback priority.

I tried doing the query above with a GROUP BY table2.table1_id, and it seemed to work, but I realised that if the best matching (first OR) was entered later in the table (higher primary ID) it would return 2nd or default priority as the grouped by row..

Any tips?

Thank you!

Answer 1

Edit:

This version uses variables to provide some sort of ranking within the available languages. Tables and test-data are not from the original question, but from the query OP provided as an answer.

It produced the expected results when I tried it:

SELECT id, description, lang
FROM
(
    SELECT ps.id, psd.description, psd.lang,
          CASE
            WHEN @id != ps.id THEN @rownum := 1 
            ELSE @rownum := @rownum + 1
          END AS rank,
          @id := ps.id
    FROM _product_sku ps
    JOIN _product_sku_data psd ON ( psd.product_sku_id = ps.id )
    JOIN ( SELECT @id:=NULL, @rownum:=0 ) x
    ORDER BY id,
             CASE WHEN lang='$this->profile_language_preference' THEN 0
                  WHEN lang='$this->browser_language' THEN 1
                  WHEN lang='default' THEN 2
                  ELSE 3
             END
) x
WHERE rank = 1;

---

Old version which did not work, since ps.id is not known in the WHERE clause:

This one should return you the rows of table1 with the "best matching" row of table2 by using LIMIT 1 and ordering languages as defined:

SELECT t1.id, t2.lang, t2.some_column
FROM table1 t1
CROSS JOIN
  ( SELECT lang, some_column
    FROM table2
    WHERE table1_id = t1.id
    ORDER BY CASE WHEN lang='fr' THEN 0
                  WHEN lang='eu' THEN 1
                  WHEN lang='default' THEN 2
                  ELSE 3
             END
    LIMIT 1
  ) t2

Answer 2

Hi, it still doesn't seem to "know" what t1.id is :(

Here follows my entire query, it goes to show table1 = _product_sku, table2 = _product_sku_data, t1 = ps, t2 = psd

SELECT ps.id, psd.description, psd.lang
                    FROM _product_sku ps                                

                    CROSS JOIN
                      ( SELECT lang, title, description
                        FROM _product_sku_data
                        WHERE product_sku_id = ps.id 
                        ORDER BY CASE WHEN lang='$this->profile_language_preference' THEN 0
                                      WHEN lang='$this->browser_language' THEN 1
                                      WHEN lang='default' THEN 2
                                      ELSE 3
                                 END
                        LIMIT 1
                      ) AS psd