On Sat, Feb 19, 2005 at 15:59:52 -0200,
Jon Lapham <lapham@jandr.org> wrote:
>
> Since I do not want to have to re-write all my aggregate function
> containing queries upon modifications to the table definitions (and I do
> not want to write multi-thousand character long SELECT statements),
> maybe it is easier to use a temp table intermediary?
>
> SELECT a.id AS aid, SUM(d.blah) AS sum_blah
> INTO TEMPORARY TABLE foo
> FROM a, b, c, d
> WHERE <some join conditions linking a,b,c,d>
>
> followed by
>
> SELECT *
> FROM a, b, c, foo
> WHERE <some join conditions linking a,b,c>
> AND foo.aid=a.id
>
> Ugly... ugly... any other ideas on how to do this? My table definitions
> LITERALLY have hundreds of columns, and I need access to them all.
Well if you are thinking about the above than you might be interested in
seeing a more sketched out example of what I was suggesting in my
followup after Tom's correction.
SELECT a.*, b.*, c.*, e.d1
FROM a, b, c,
(SELECT a.id AS a1, b.id AS b1 , c.id AS c1, sum(d) AS d1
FROM a, b, c, d
WHERE <some join conditions linking a,b,c,d>
GROUP BY a1, b1, c1) AS e
WHERE
a.id = e.a1 AND
b.id = e.b1 AND
c.id = e.c1
;