Re: UNION not working... why?

Hello

use derived tables

SELECT *
FROM (SELECT c.name, d.year_start, d.value
FROM emissions_so2_total_rivm AS d
LEFT JOIN
countries AS c ON c.id = d.id_country
WHERE ((c.iso_2_code = 'CH') OR (c.iso_3_code = 'CH'))
ORDER BY d.year_start DESC
LIMIT 1) c1
UNION ALL
SELECT *
FROM (SELECT c.name, d.year_start, d.value
FROM emissions_so2_total_rivm AS d
LEFT JOIN
countries AS c ON c.id = d.id_country
WHERE ((c.iso_2_code = 'CH') OR (c.iso_3_code = 'CH'))
ORDER BY d.year_start DESC
LIMIT 1) c2

Regards
Pavel Stehule

On 13/12/2007, Stefan Schwarzer <stefan(dot)schwarzer(at)grid(dot)unep(dot)ch> wrote:
>
> Hi there,
>
> I have two rather simple queries, which I would to UNION, but somehow I
> always get an error message for the UNION ("ERROR: syntax error at or near
> "UNION"")
>
> Each query selects the country name ("Switzerland" in this case), the most
> recent year in the table and its value.
>
> What is wrong with it? Thanks for any hints!
>
>
> SELECT
> c.name,
> d.year_start,
> d.value
> FROM
> emissions_so2_total_rivm AS d
> LEFT JOIN
> countries AS c ON c.id = d.id_country
> WHERE
> ((c.iso_2_code = 'CH') OR (c.iso_3_code = 'CH'))
> ORDER BY
> d.year_start DESC
> LIMIT 1
>
> UNION ALL
>
> SELECT
> c.name,
> d.year_start,
> d.value
> FROM
> pop_density AS d
> LEFT JOIN
> countries AS c ON c.id = d.id_country
> WHERE
> ((c.iso_2_code = 'CH') OR (c.iso_3_code = 'CH'))
> ORDER BY
> d.year_start DESC
> LIMIT 1
>
>
>
>
> ____________________________________________________________________
>
>
> Stefan Schwarzer
>
> Lean Back and Relax - Enjoy some Nature Photography
> http://photoblog.la-famille-schwarzer.de
>
> Appetite for Global Data? UNEP GEO Data Portal:
> http://geodata.grid.unep.ch
>
> ____________________________________________________________________
>
>
>
>
>
>