From: Peter Brawley
Date: March 22 2006 2:46pm
Subject: Re: mysql query and version problem .... Help!
List-Archive: http://lists.mysql.com/mysql/196059
Message-Id: <4421635D.8000002@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------080707010402060307010901"

--------------080707010402060307010901
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Gregory

 >I thought about that but I'm worried about the users getting the rite data
 >if multiple users make the same requests at the same time ... i supose the
 >easiest would be to name the temp tables after the user making the request
 >??

A temp table is visible only in the thread where it's been created, and 
vanishes when the connection closes.

PB

-----

Gregory Machin wrote:
> On 3/21/06, Addison, Mark <Mark.Addison@stripped> wrote:
>   
>>> -----Original Message-----
>>> From: Gregory Machin [mailto:gregory.machin@stripped]
>>> Sent: 21 March 2006 11:28
>>> To: mysql@stripped
>>> Subject: mysql query and version problem .... Help!
>>>
>>> Hi.
>>>
>>> I have just found out that my hosting provider is using mysql
>>> 4 and I'm
>>> using mysql 5 the one query I need wont work and is a key
>>> feature in the
>>> application .. here is the query i'm using
>>>
>>> SELECT dealer_id, auto_id, bid_amount FROM bids b1 WHERE
>>> bid_amount=(SELECT
>>> MAX(b2.bid_amount)
>>> FROM bids b2 WHERE b1.auto_id = b2.auto_id) AND
>>> auto_dealer_id = '3' AND
>>> Bid_Status = '1';
>>>
>>> How do I get this to work on version 4 ?
>>>       
>> You could create a tmp table with the max bids and then join on
>> that. Something like:
>>
>> CREATE TEMPORARY TABLE max_bids
>>     SELECT auto_id, MAX(bid_amount) AS max_bid_amount
>>     FROM bids
>>     GROUP BY auto_id;
>> SELECT dealer_id, b1.auto_id, bid_amount FROM bids b1, max_bids b2
>>     WHERE b1.auto_id = b2.auto_id
>>     AND bid_amount=max_bid_amount
>>     AND auto_dealer_id = '3' AND Bid_Status = '1';
>>
>> mark
>> --
>>     
>
>
> I thought about that but I'm worried about the users getting the rite data
> if multiple users make the same requests at the same time  ...  i supose the
> easiest would be to name the temp tables after the user making the request
> ??
>   
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.385 / Virus Database: 268.2.6/287 - Release Date: 3/21/2006
>   

--------------080707010402060307010901--