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?
[MySQL] "Degradation" query
Moderator: General Moderators
Re: [MySQL] "Degradation" query
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) -
This is untested, but should give you the overall gist of it
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')Re: [MySQL] "Degradation" query
It worked! Thank you very much. I was trying to do it with ordering and GROUP BY, but nothing 
Re: [MySQL] "Degradation" query
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
Nice, good to know 
Re: [MySQL] "Degradation" query
GoooooooooodWeirdan wrote:CASE can be even further simplified by replacing it with COALESCE()