Re: [GENERAL] "Concatenate" two queries - how?

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, ..."

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

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

               http://www.postgresql.org/docs/faq Received on Wed Oct 24 09:32:34 2007