Page 1 of 1

[MySQL] "Degradation" query

Posted: Tue Sep 07, 2010 2:49 am
by lorenzo-s
Hi everyone. I have to build a tricky MySQL query. I have a language table that looks like:
[text]| ID | LANG | CODE | DATA |
|----|------|--------|--------|
| 1 | en | title | Title |
| 2 | it | title | Titolo |
| 3 | en | text | Lorem |
| 4 | en | thanks | Thanks |
| 5 | it | thanks | Grazie |
| 6 | fr | thanks | Merci |[/text]Where ID is PRIMARY KEY and (LANG, CODE) is UNIQUE.

English text is always present for each CODE, but other languages can be incomplete. Now, let's suppose my current language is italian (it). I want to get italian DATA for each code, and if there are codes with no italian row, I want to get english DATA. So, I want this result:
[text]| CODE | DATA |
|--------|--------|
| title | Titolo |
| text | Lorem |
| thanks | Grazie |[/text]Where text -> Lorem is loaded from the english en row because there is not the corresponding italian it.

Subqueries and other devilments are welcome. Is that possible?

Re: [MySQL] "Degradation" query

Posted: Tue Sep 07, 2010 3:26 am
by Eran
It is possible. Query the table twice (using a join), and use a case statement to decide whether to put English text or other language. Something like the following (assuming your table is named 'titles' and you want Italian text) -

Code: Select all

SELECT (CASE WHEN it.`data` IS NULL THEN en.`data` ELSE it.`data` END) AS title 
FROM titles AS en
LEFT JOIN titles AS it ON it.code=en.code AND it.lang='it'
WHERE en.lang='en'
AND en.code IN ('title','text','thanks')
This is untested, but should give you the overall gist of it

Re: [MySQL] "Degradation" query

Posted: Tue Sep 07, 2010 3:56 am
by lorenzo-s
It worked! Thank you very much. I was trying to do it with ordering and GROUP BY, but nothing :)

Re: [MySQL] "Degradation" query

Posted: Tue Sep 07, 2010 7:50 am
by Weirdan
CASE can be even further simplified by replacing it with COALESCE():

Code: Select all

SELECT COALESCE(it.`data`, en.`data`) AS title 
FROM titles AS en
LEFT JOIN titles AS it ON it.code=en.code AND it.lang='it'
WHERE en.lang='en'
AND en.code IN ('title','text','thanks')

Re: [MySQL] "Degradation" query

Posted: Tue Sep 07, 2010 7:53 am
by Eran
Nice, good to know :)

Re: [MySQL] "Degradation" query

Posted: Tue Sep 07, 2010 8:59 am
by lorenzo-s
Weirdan wrote:CASE can be even further simplified by replacing it with COALESCE()
Gooooooooood :mrgreen: