List:General Discussion« Previous MessageNext Message »
From:Gregory Machin Date:March 22 2006 8:40am
Subject:Re: mysql query and version problem .... Help!
View as plain text  
It's working ok ..
But I have one problem ..
I'm getting the following error "Table 'max_bids3' already exists";

but I get the following ...
mysql> DROP TABLE max_bids3;
ERROR 1051 (42S02): Unknown table 'max_bids3'

What do I do to fix this, and how can I see the temp tables ?


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
> --
>
>
> Please Note:
>
>
>
> Any views or opinions are solely those of the author and do not
> necessarily represent
> those of Independent Television News Limited unless specifically stated.
> This email and any files attached are confidential and intended solely for
> the use of the individual
> or entity to which they are addressed.
> If you have received this email in error, please notify
> postmaster@stripped.uk
>
> Please note that to ensure regulatory compliance and for the protection of
> our clients and business,
> we may monitor and read messages sent to and from our systems.
>
> Thank You.
>
>


--
Gregory Machin
greg@linuxpro.co.za
gregory.machin@stripped
www.linuxpro.co.za
www.exponent.co.za
Web Hosting Solutions
Scalable Linux Solutions
www.iberry.info (support and admin)

+27 72 524 8096
Thread
mysql query and version problem .... Help!Gregory Machin21 Mar
RE: mysql query and version problem .... Help!Mark Addison21 Mar
  • Re: mysql query and version problem .... Help!Gregory Machin22 Mar
    • Re: mysql query and version problem .... Help!Peter Brawley22 Mar
  • Re: mysql query and version problem .... Help!Gregory Machin22 Mar
    • Re: mysql query and version problem .... Help!Gabriel PREDA22 Mar