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.schwarzer@grid.unep.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
>
> ____________________________________________________________________
>
>
>
>
>
>