Thread: 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
Attachment
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
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
NotDashEscaped: You need GnuPG to verify this message
> 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)?
Basically, the latter. Your final query on the page is:
SELECT name, productaspectId,
(SELECT count(*) from ProductaspectLabel P2
WHERE P.productaspectId = P2.productaspectId) AS labelcount,
(SELECT count(*) from ProductaspectLegend P3
WHERE P.productaspectId = P3.productaspectId) AS legendcount
FROM Productaspect P ORDER BY name ASC;
but you say:
-- This is correct, but I'm fearing about the speed of those sub-selects
-- since I have other structures that have much more tables that are
-- linked for whom the count has to be calculated
I don't think the subselects will perform as bad as you fear, but it
is hard to tell without knowing more about your data. It depends on how
big each table is and on how often they get updated. I suspect the above
query is about as good as you are going to get if the data in those
tables changes rapidly.
However, in addition to making views (which should help), you could store
each count in another table and join off of that:
SELECT productaspectid, count(productaspectid)
INTO labelcount FROM productaspectlabel GROUP BY 1;
SELECT productaspectid, count(productaspectid)
INTO legendcount FROM productaspectlegend GROUP BY 1;
Slap on some indexes:
CREATE INDEX labelid ON labelcount(productaspectid);
CREATE INDEX legendid ON legendcount(productaspectid);
and your query is now:
SELECT p.name, p.productaspectId,
COALESCE(labelcount.count,0) AS labelcount,
COALESCE(legendcount.count,0) AS legendcount
FROM Productaspect p
LEFT OUTER JOIN labelcount USING (productaspectId)
LEFT OUTER JOIN legendcount USING (productaspectId)
ORDER BY p.name ASC;
We've moved the aggregate functions out of the query and into a table:
the hard part is updating those tables. If this is a data warehouse
and the the data comes in by batch, just rerun the SELECT INTO
statements above, or have a more intelligent trigger that updates
the "count" tables as needed. As I said before, however, if those
tables are changing rapidly or they are fairly small, the original
query above is probably your best bet.
--
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200301161157
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html
iD8DBQE+JuU5vJuQZxSWSsgRAjnBAJ9w8yubyPWMb50/QCANHuf7puuUwgCgv5Hp
OqbHDgKT57jXB6wrHqg3QrA=
=siUq
-----END PGP SIGNATURE-----
Thanks a lot for your very interesting answer. I combined some of your suggestions with others I received and updated the page with the question. Thanks a lot to everyone for helping out on this one. On Thu, 2003-01-16 at 17:49, greg@turnstep.com wrote: > > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > NotDashEscaped: You need GnuPG to verify this message > > > > 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)? > > Basically, the latter. Your final query on the page is: -- 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