I think this query will do the trick but don't guarantee performance....
SELECT T1.name,
T1.productaspectId,
labelcount,
legendcount
FROM Productaspect T1,
( SELECT Productaspect.productaspectId, count(label) as labelcount FROM
Productaspect LEFT OUTER JOIN ProductaspectLabel ON (
ProductaspectLabel.productaspectId = Productaspect.productaspectId ) GROUP
BY Productaspect.productaspectId) T2,
( SELECT Productaspect.productaspectId, count(legend) as legendcount
FROM Productaspect LEFT OUTER JOIN ProductaspectLegend ON (
productaspectlegend.productaspectId = Productaspect.productaspectId ) GROUP
BY Productaspect.productaspectId) T3
WHERE T2.productaspectId=T1.productaspectId
AND T3.productaspectId=T1.productaspectId
----------------------------------------------------------------------------
---------------
Patrick Fiche
email : patrick.fiche@aqsacom.com
tél : 01 69 29 36 18
----------------------------------------------------------------------------
---------------
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Geert Bevin
Sent: Thursday, January 16, 2003 4:23 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Unsolveable query?
Hello,
the details of my problem are described at
http://uwyn.com/greenenergy.txt
I basically have a set of tables with one product table and other tables
that contain labels and legends for each row in the product table. I'm
looking for a way to write one query that retrieves all the products and
the count of labels and legends, 0 should be returned if none are
present. The fact of doing several joins and aggregate functions
together returns undesired results.
Can anyone solve this query, or do I have to write a count query for
each linked table (one for labels and one for legends)?
Thanks a lot for the help,
Geert
--
Geert Bevin Uwyn
"Use what you need" Lambermontlaan 148
http://www.uwyn.com 1030 Brussels
gbevin@uwyn.com Tel & Fax +32 2 245 41 06
PGP Fingerprint : 4E21 6399 CD9E A384 6619 719A C8F4 D40D 309F D6A9
Public PGP key : available at servers pgp.mit.edu, wwwkeys.pgp.net