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