Re: mysql query and version problem .... Help!
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 [EMAIL PROTECTED] wrote: -Original Message- From: Gregory Machin [mailto:[EMAIL PROTECTED] Sent: 21 March 2006 11:28 To: mysql@lists.mysql.com 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] [EMAIL PROTECTED] www.linuxpro.co.za www.exponent.co.za Web Hosting Solutions Scalable Linux Solutions www.iberry.info (support and admin) +27 72 524 8096
Re: mysql query and version problem .... Help!
You don't need to drop a TEMPORARY table... it is dropped at connection-close ! You don't need to wory about different names for TEMPORARY tables... Manual says: A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. Good luck !
Re: mysql query and version problem .... Help!
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 [EMAIL PROTECTED] wrote: -Original Message- From: Gregory Machin [mailto:[EMAIL PROTECTED] Sent: 21 March 2006 11:28 To: mysql@lists.mysql.com 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
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 ? Many thanks -- Gregory Machin [EMAIL PROTECTED] [EMAIL PROTECTED] www.linuxpro.co.za www.exponent.co.za Web Hosting Solutions Scalable Linux Solutions www.iberry.info (support and admin) +27 72 524 8096
RE: mysql query and version problem .... Help!
-Original Message- From: Gregory Machin [mailto:[EMAIL PROTECTED] Sent: 21 March 2006 11:28 To: mysql@lists.mysql.com 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 [EMAIL PROTECTED] 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query and version problem .... Help!
On 3/21/06, Addison, Mark [EMAIL PROTECTED] wrote: -Original Message- From: Gregory Machin [mailto:[EMAIL PROTECTED] Sent: 21 March 2006 11:28 To: mysql@lists.mysql.com 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 ??
