Re: Unsolveable query?

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(dot)fiche(at)aqsacom(dot)com
tél : 01 69 29 36 18
----------------------------------------------------------------------------
---------------

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Geert Bevin
Sent: Thursday, January 16, 2003 4:23 PM
To: pgsql-general(at)postgresql(dot)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(at)uwyn(dot)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