"Concatenate" two queries - how?

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.