[MySQL] "Degradation" query
Posted: Tue Sep 07, 2010 2:49 am
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?
[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?