Re: Unsolveable query?
От | Patrick Fiche |
---|---|
Тема | Re: Unsolveable query? |
Дата | |
Msg-id | 85058ADF852DD5118FD50002A528A5B6079B37@SERVEUR обсуждение исходный текст |
Ответ на | Unsolveable query? (Geert Bevin <gbevin@uwyn.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: