I'm wondering how to get "all rows where col='X'; if there are none, all rows where col='Y'"

Simplfied database;

CREATE TABLE CHARACTER_NAMES(CHARACTER_ID, LANG VARCHAR(3), NAME);

INSERT INTO CHARACTER_NAMES(1, "ENG", "DONALD DUCK");
INSERT INTO CHARACTER_NAMES(1, "ENG", "GOOD OL' DONALD");
INSERT INTO CHARACTER_NAMES(1, "SWE", "KALLE ANKA");
INSERT INTO CHARACTER_NAMES(1, "SWE", "KALLEN");
INSERT INTO CHARACTER_NAMES(2, "ENG", "MICKEY MOUSE");
INSERT INTO CHARACTER_NAMES(2, "SWE", "MUSSE PIGG");
INSERT INTO CHARACTER_NAMES(2, "SWE", "MUSEN");
INSERT INTO CHARACTER_NAMES(3, "ENG", "GOOFY");
INSERT INTO CHARACTER_NAMES(3, "NOR", "FEDTMULE");

(It's a bit forced that the characters have several names in the same language, but that's how the real database looks like. Also, "CHARACTER_ID" is also a foreign key to the CHARACTER table, but that's not part of the problem, so omitted.)

The user has a language setting, and when there is a database query for a specific character, the query should return the names in the selected language, or the names in English, if the selected language has no results. In the above example, if the setting was "Swedish" and the users selected character 3 (Goofy) the name search should return "Goofy", as there is no Swedish name registered. If the user selected Mickey Mouse, the search should return 2 rows: "Musse Pigg" and "Musen".

I wonder if this is possible to express in an SQL query.

If I just wanted the FIRST in the selected language, if none, english, I could use:

SELECT NAME FROM CHARACTER_NAMES
   WHERE CHARACTER_ID=?
   ORDER BY CASE
      WHEN LANG='NOR' THEN 1
      WHEN LANG='ENG' THEN 2
   END
LIMIT 1;

But as I can't know how many names there will be in the selected language, I have to let this LIMIT vary, and I don't really know how to do that in a nice and proper way.

CodePudding user response：

I'm wondering how to get "all rows where col='X'; if there are none, all rows where col='Y'"

SELECT * 
FROM table 
WHERE col='X'

UNION ALL

SELECT * 
FROM table 
WHERE col='Y' 
  AND NOT EXISTS ( SELECT NULL
                   FROM table 
                   WHERE col='X' )

If a row(s) with col='X' exists then WHERE EXISTS will give FALSE and 2nd subquery will return nothing - i.e. only output of 1st subquery only will be returned.

And backward, if there is no row with col='X' then 1st subquery won't return rows, but WHERE EXISTS will give TRUE and 2nd subquery will return all rows with col='Y' - i.e. only output of 2nd subquery only will be returned.

Or you may use

SELECT *
FROM table
WHERE col = CASE WHEN EXISTS ( SELECT NULL
                               FROM table 
                               WHERE col='X' )
                 THEN 'X'
                 ELSE 'Y'
                 END; 

There are more variants, of course...

CodePudding user response：

In MySQL 8 you can use CASE expression with RANK to get the desired result:

WITH cte AS (
    SELECT *, RANK() OVER (PARTITION BY character_id ORDER BY CASE
        WHEN lang = 'NOR' THEN 1
        WHEN lang = 'ENG' THEN 2
        ELSE 3
    END) AS rnk
    FROM character_names
)
SELECT *
FROM cte
WHERE rnk = 1

Identical result could be achieved with NOT EXISTS:

SELECT *
FROM character_names AS t1
WHERE lang = 'NOR'
OR lang = 'ENG' AND NOT EXISTS (
    SELECT *
    FROM character_names AS t2
    WHERE t2.character_id = t1.character_id
    AND t2.lang = 'NOR'
)

Result: