Hi!
I am struggling with a particular select, trying to get it to execute as
efficiently as possible. I have tables MASTER(ID, NAME, ...) and
DETAIL(ID, MASTER_ID, VALUE1, VALUE2, VALUE3). The SELECT I want to get
would display all fields from MASTER and an aggregation of fields from
DETAIL, e.g. something like:
SELECT MASTER.*,
(SELECT SUM(VALUE1) FROM DETAIL WHERE DETAIL_ID=MASTER.ID),
(SELECT MAX(VALUE2) FROM DETAIL WHERE DETAIL_ID=MASTER.ID),
(SELECT MIN(VALUE1) FROM DETAIL WHERE DETAIL_ID=MASTER.ID)
FROM MASTER
WHERE NAME STARTING WITH 'A'
I would like to replace the three similar subselects with one
three-field subselect, but my attempts at using CTE rather failed:
WITH DETAIL_AGGREGATE (MASTER_ID, VALUE1, VALUE2, VALUE3)
AS (SELECT MASTER_ID, SUM(VALUE1), MAX(VALUE2), MIN(VALUE3)
FROM DETAIL
GROUP BY MASTER_ID)
SELECT MASTER.*, DETAIL_AGGREGATE.*
FROM MASTER
JOIN DETAIL_AGGREGATE ON MASTER_ID=MASTER.ID
WHERE NAME STARTING WITH 'A'
This is very slow, presumably because it calculates the aggregates over
all rows in DETAIL, regardless of whether the rows are actually included
in the result.
I could probably write a traditional:
SELECT MASTER.ID, MASTER.NAZEV, MASTER.xyz, ...,
SUM(DETAIL.VALUE1), MAX(DETAIL.VALUE2), MIN(DETAIL.VALUE3)
FROM MASTER
LEFT JOIN DETAIL ON DETAIL.MASTER_ID=MASTER.ID
WHERE MASTER.NAME STARTING WITH 'A'
GROUP BY 1, 2, 3, ...
But there are specific conditions which make this kind of SELECT rather
undesirable.
I still think that CTEs are the way to go, but I can't figure out how to
write them efficiently.
Can anyone help, please?
Thanks,
Josef
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !
Also search the knowledgebases at http://www.ibphoenix.com
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
firebird-support-dig...@yahoogroups.com
firebird-support-fullfeatu...@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
