Обсуждение: "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
Use "union" ??? -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Stefan Schwarzer Sent: Wednesday, October 24, 2007 9:09 AM To: pgsql-general@postgresql.org Subject: [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
am Wed, dem 24.10.2007, um 15:08:51 +0200 mailte Stefan Schwarzer folgendes: > 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! select ... UNION select ... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
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: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] 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
In article <20071024132516.GI19546@a-kretschmer.de>, "A. Kretschmer" <andreas.kretschmer@schollglas.com> writes: > am Wed, dem 24.10.2007, um 15:08:51 +0200 mailte Stefan Schwarzer folgendes: >> 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! > select ... UNION select ... Apparently Stefan doesn't know about UNION, and thus he probably doesn't know that UNION ALL is almost always preferrable.