Krzysztof Kotowicz wrote:
Hi!
I'm porting some MySQL code to SQLite 2.8.16.
I'm having problems with a few multi-tables queries used by my application:
The problems seems to be related with inability of including "calculated
field" names in ORDER BY and/or HAVING clauses, although I might be
wrong (and most probably I am) .
The simplest of the queries:
SELECT countries.*,
coalesce(cd1.country_name, cd2.country_name) AS country_name,
coalesce(cd1.language_id, cd2.language_id) AS language_id
FROM countries
LEFT JOIN countries_description AS cd1 ON (countries.country_id=cd1.country_id AND cd1.language_id=1)
LEFT JOIN countries_description AS cd2 ON (countries.country_id=cd2.country_id AND cd2.language_id=0)
WHERE 1
GROUP BY countries.country_id
HAVING 1
ORDER BY country_name ASC;
Try this?
SELECT countries.*,
coalesce(cd1.country_name, cd2.country_name) AS country_name,
coalesce(cd1.language_id, cd2.language_id) AS language_id
FROM countries
LEFT JOIN countries_description AS cd1 ON (countries.country_id=cd1.country_id AND cd1.language_id=1)
LEFT JOIN countries_description AS cd2 ON (countries.country_id=cd2.country_id AND cd2.language_id=0)
WHERE 1
GROUP BY countries.country_id
HAVING 1
ORDER BY coalesce(cd1.country_name, cd2.country_name) ASC;
John LeSueur
