How to assign a variable without returning results

[Sean Nolan]

I have a batch of SQL code, where I need to calculate an aggregate in a 
SELECT and then use that value in a second SELECT. I am using MySql 4.0 so 
unfortunately I cannot use a subquery (which is how I'd like to do this 
:-)).

So I run the first SELECT and assign the aggregate to a variable something 
like this:
SELECT @AveragePrice := AVG(price) FROM Products;

Then I run the second SELECT, something like this:
SELECT * FROM Products WHERE price > @AveragePrice;
That works just fine, but I now get two result sets returned to the client, 
and I have to ignore the first one and use the second one.

I have found a workaround, in that I now use a CREATE TEMPORARY TABLE ... 
SELECT to run the first SELECT. It assigns the variable correctly, and then 
I run the second SELECT and I just get one result set on the client. It 
seems like a bad idea to create a temporary table just to prevent the 
connector from getting two result sets, but I've not figured out any other 
way to do it. Is there a better way to assign the aggregate value to the 
variable without returning a result set from the first SELECT?

Thanks
Sean
_________________________________________________________________
Don’t just search. Find. Check out the new MSN Search! 
http://search.msn.click-url.com/go/onm00200636ave/direct/01/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]