Re: [GENERAL] "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

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related postgresql.org Links pgsql-admin pgsql-advocacy pgsql-announce pgsql-bugs pgsql-cygwin pgsql-docs pgsql-general pgsql-interfaces pgsql-jdbc pgsql-jobs pgsql-novice pgsql-odbc pgsql-performance pgsql-php pgsql-ports pgsql-sql Request more information about Pantek

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)---------------------------

               http://archives.postgresql.org/ Received on Wed Oct 24 09:30:01 2007