[MySQL] "Degradation" query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
lorenzo-s
Forum Commoner
Posts: 43
Joined: Tue Aug 25, 2009 12:25 pm

[MySQL] "Degradation" query

Post 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?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: [MySQL] "Degradation" query

Post 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
User avatar
lorenzo-s
Forum Commoner
Posts: 43
Joined: Tue Aug 25, 2009 12:25 pm

Re: [MySQL] "Degradation" query

Post by lorenzo-s »

It worked! Thank you very much. I was trying to do it with ordering and GROUP BY, but nothing :)
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: [MySQL] "Degradation" query

Post 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')
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: [MySQL] "Degradation" query

Post by Eran »

Nice, good to know :)
User avatar
lorenzo-s
Forum Commoner
Posts: 43
Joined: Tue Aug 25, 2009 12:25 pm

Re: [MySQL] "Degradation" query

Post by lorenzo-s »

Weirdan wrote:CASE can be even further simplified by replacing it with COALESCE()
Gooooooooood :mrgreen:
Post Reply