Operations within group when GROUP BY

Question

The key of table items is comprised of (lang, id). Some items have a translated name in different languages. I want to create such a query that would return all the items from the DB in a given language and if an item doesn't have a translation to return the untranslated value.

Say I want to get all items written in Bulgarian. This is where I'm stuck:

SELECT lang, id, name
FROM items
WHERE lang = "bg" OR lang = "en"
GROUP BY id

The problem arises when there is an item, say, (1, "en") and an item (1, "bg"). The ids are the same. Then how does MySQL or SQLite determine which result to return? Is there any way I can tell it that I would rather prefer to return (1, "bg") if it exists but if it doesn't then (1, "en") would satisfy me?

P.S. To further illustrate what I want let's imagine that the database contains the following entries with schema (id, lang, name):

(1, "en", "abc")

(2, "en", "cde")

(3, "en", "def")

(1, "bg", "абв")

(3, "bg", "жзи")

After executing the desired query for Bulgarian I should get:

(1, "bg", "абв")

(2, "en", "cde")

(3, "bg", "жзи")

score 4 · Accepted Answer · answered Nov 27 '11 at 23:49

If "untranslated" means "English" or in other words, the base language is English, you can LEFT join the table to itself and use COALESCE() function to get rid of NULL values

SELECT COALESCE(bg.lang, en.lang) AS lang
     , en.id                      AS id
     , COALESCE(bg.name, en.name) AS name
FROM items en
  LEFT JOIN items bg
    ON  bg.id = en.id
    AND bg.lang = 'bg'
WHERE en.lang = 'en'

score 1 · Answer 2 · answered Nov 27 '11 at 23:53

1

Standard SQL does not let you select columns in "group by" queries that do not either (1) appear on the group by list, or (2) included in an aggregate function. Any industrial strength SQL engine (DB2, Oracle, SQL Server) would consider your query incorrect.

In cases when you need to choose a specific item or a default when it is not in the database, the coalesce function is used. With this function in hand, you should be able to formulate your query without "group by".

answered Nov 27 '11 at 23:53

Sergey Kalinichenko

714,442
84
1,110
1,523

Actually standard SQL-2003 allows to be included in SELECT (3): columns functionally dependent on the GROUP BY columns: http://stackoverflow.com/questions/7594865/why-does-mysql-add-a-feature-that-conflicts-with-sql-standards/7596265#7596265 – ypercubeᵀᴹ Nov 28 '11 at 00:05
The OP's query though, would still be invalid, even with that standard. – ypercubeᵀᴹ Nov 28 '11 at 00:06

score 0 · Answer 3 · answered Nov 27 '11 at 23:47

0

You can do GROUP BY on multiple columns:

SELECT lang, id, name
FROM items
WHERE lang = "bg" OR lang = "en"
GROUP BY id, lang

answered Nov 27 '11 at 23:47

Polynomial

27,674
12
80
107

I know, but that would return both (*1*, *"bg"*) and (*1*, *"en"*). I actually don't want to have results with mutiple ids. – Martin Marinov Nov 27 '11 at 23:49

Operations within group when GROUP BY

3 Answers3

Linked