I don't know whether I did understand you entirely, but you might want to
take a look at the UNION clause:

http://www.postgresql.org/docs/8.2/interactive/sql-select.html#SQL-UNION



-----Ursprüngliche Nachricht-----
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Im Auftrag von Stefan Schwarzer
Gesendet: Mittwoch, 24. Oktober 2007 15:09
An: pgsql-general@postgresql.org
Betreff: [GENERAL] "Concatenate" two queries - how?

Hi there,

I have two queries, which I would like to bring together to form one  
result.

The first query is a simple SELECT on a table of national statistics.

SELECT
        COALESCE(c.name, ''''),
        year_start AS year,
        value
FROM
        fish_catch AS d
LEFT JOIN
        countries AS c ON c.id = id_country
WHERE
        (year_start = 1995 OR year_start = 2000 ) AND
        (name = 'Afghanistan' OR name = 'Albania'  )


The second query is an aggregation-on-the-fly of these national  
statistics to its regions. The result is for example not "Germany,  
France, Algeria, ...", but "Europe, Africa, ..."

SELECT
        COALESCE(r.name, ''''),
        year_start AS year,
        SUM(value) AS value
FROM
        life_expect AS d
LEFT JOIN
        countries_view AS c ON c.id = id_country
RIGHT JOIN
        regions AS r ON r.id = c.reg_id
WHERE
        (year_start = 1995 OR year_start = 2000 ) AND
        (r.name = 'Europe')
GROUP BY
        r.name, year_start


Now, I want to enable queries which display national as well as  
regional values. I could probably work with independent queries, but  
I think it would be "cleaner" and more efficient to get everything  
into a single query.

Can someone give me a hint how this would work?

Thanks a lot!

Stef


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

Reply via email to