man i didnt even know you can do this
AND s.date > q.date
i assumed that goes in a where clause ?
> -----Original Message-----
> From: news [mailto:news@stripped]On Behalf Of Harald Fuchs
> Sent: Wednesday, May 26, 2004 8:01 PM
> To: mysql@stripped
> Subject: Re: need help with a complicated join
>
>
> In article <puy8nf8r1b.fsf@stripped>,
> Harald Fuchs <hf517@stripped> writes:
>
> > SELECT q.symbol, q.date,
> > q.quote * product (s.split_from / s.split_to) AS adjusted_quote
> > FROM quotes q
> > LEFT JOIN splits s ON s.symbol = q.symbol AND s.date > q.date
> > GROUP BY q.symbol, q.date, q.quote
> > ORDER BY q.symbol, q.date
>
> > The problem is how to define the 'product' aggregate (along the lines
> > of 'sum').
>
> [ I like talking to myself :-) ]
>
> A workaround for the missing product aggregate would be
>
> SELECT q.symbol, q.date, q.quote,
> q.quote * exp(sum(log(coalesce(s.split_from/s.split_to,1))))
> FROM quotes q
> LEFT JOIN splits s ON s.symbol = q.symbol AND s.date > q.date
> GROUP BY q.symbol, q.date, q.quote
> ORDER BY q.symbol, q.date
>
> but don't ask me how that performs...
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/mysql?unsub=1
