Dan Rossi <[EMAIL PROTECTED]> wrote on 12/27/2005 11:39:57 PM: > Hi there i am trying to use usewr variables in a select statement to > add to a where clause in a sub query. Ie > > select @id:=id,@month:=month, (select SUM(totals) from table where > [EMAIL PROTECTED] and [EMAIL PROTECTED]) as totals from table > > its happened on other occasions ie with calculations and sums, whats > happened in mysql5 ? It used to work in mysql4 , something i am doing > is wrong ? Please let me know thanks. > >
Is there a great reason why you are using a subquery? I could rewrite this to avoid the subquery and probably eliminate your particular problem: SELECT id, month, sum(totals) totals FROM TABLE group by id, month; Unless(!) you oversimplified your original example. In which case, you should post your actual query and I can give you a better response. Technically, the values of the variables should not be determined until AFTER the row is processed which means that you shouldn't be able to use them for your subquery (at least that's how I remember the SQL:2003 spec but it's late and I could very well be wrong in my recollection) Personally, I am not that big a fan of subqueries anyway. There are a few types of queries where they make the SQL to achieve a result rather compact and elegant. However, I have never seen a subquery actually outperform a properly constructed linear query. They sometimes match linear performance but most often perform worse to much worse. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
