CODEWITHSUNDEEP
mysqlsql
Vadiem Janssens
Vadiem Janssens

Reputation: 4109

Select one or the other row from a table, depending on if other row exists

I'd like to select translations for products by a given language, but also include a fallback when the translation is not present. This is the dataset:

+----+------------+---------------+-----------------+
| id | product_id | language_code |      name       |
+----+------------+---------------+-----------------+
|  1 |          1 | en            | Name in english |
|  2 |          1 | fr            | Name in french  |
|  3 |          1 | es            | Name in spanish |
|  4 |          2 | en            | Another product |
+----+------------+---------------+-----------------+

I'm working on a query which selects language_code='fr' and fallback to 'en' if 'fr' is not present. The result should be something along the lines of:

+----+------------+---------------+-----------------+
| id | product_id | language_code |      name       |
+----+------------+---------------+-----------------+
|  2 |          1 | fr            | Name in french  |
|  4 |          2 | en            | Another product |
+----+------------+---------------+-----------------+

I've tried multiple things:

SELECT translations.id, translations.language_code, translations.name
FROM translations
WHERE language_code = 'fr' 
OR language_code = 'en';

This will limit to the given languages, but does not remove duplicates (id=1).

I've seen multiple solutions with GROUP BY, but I keep running in only_full_group_by issues, which I don't know how to fix.

SELECT *
FROM translations
WHERE language_code = 'fr' 
OR language_code = 'en'
GROUP BY product_id;

Upvotes: 2

Views: 93

Answers (2)

fifonik
fifonik

Reputation: 1606

If English names available for all products you can try to use LEFT JOIN:

SELECT
      e.product_id
    , IF(f.name IS NOT NULL, f.name, e.name) AS name
FROM
    translations AS e
    LEFT JOIN translations AS f ON (
            f.product_id = e.product_id
        AND f.language_code = 'fr'
    )
WHERE
    e.language_code = 'en'
;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can use not exists and or:

select t.id, t.language_code, t.name
from translations t
where t.language_code = 'fr' OR
      (t.language_code = 'en' and
       not exists (select 1 from translations t2 where t2.product_id = t.product_id and t2.language_code = 'fr'
      ));

Upvotes: 2

Related Questions