12 messages in com.mysql.lists.mysqlRe: Rewriting subquery for old MySQL
FromSent OnAttachments
Alex Gemmell30 Nov 2005 07:05 
rouvas30 Nov 2005 07:18 
SGr...@unimin.com30 Nov 2005 07:47 
Alex Gemmell30 Nov 2005 08:10 
Dan Nelson30 Nov 2005 08:26 
Alex Gemmell30 Nov 2005 08:40 
Alex Gemmell30 Nov 2005 08:48 
Michael Stassen30 Nov 2005 08:55 
Alex Gemmell30 Nov 2005 09:01 
SGr...@unimin.com30 Nov 2005 09:02 
Michael Stassen30 Nov 2005 09:02 
Alex Gemmell06 Dec 2005 07:27 
Subject:Re: Rewriting subquery for old MySQL
From:SGr...@unimin.com (SGr@unimin.com)
Date:11/30/2005 07:47:51 AM
List:com.mysql.lists.mysql

Alex Gemmell <agem@gmail.com> wrote on 11/30/2005 10:06:09 AM:

Hi,

Unfortunately I need to use a query on an old MySQL (4.0.xx) and the one

I currently have uses a subquery. So it works on my 4.1 but not with this 4.0. I have read in the MySQL manual that I can rewrite subqueries

using joins but I don't see how with this query because I only have one table to check.

Basically all I'm doing is pulling out the most recent 10 activities stored in a table, then reversing the order so it appears oldest first, to newest (note the "$row['Assignment_ID']" is just a PHP variable):

SELECT * FROM (SELECT * FROM tblactivities WHERE Assignment_ID=$row['Assignment_ID'] ORDER BY Date DESC LIMIT 10) AS tblTemp ORDER BY Date ASC

This works perfectly on 4.1. How can I rewrite this to get the same effect without using a subquery and so allow it to work on 4.0?

Any help gratefully recieved!

Alex

To make this work on 4.0, you have to do manually something the engine did for you implicitly: create a temporary table.

CREATE TEMPORARY TABLE tmpSubquery SELECT * FROM tblactivites WHERE Assignment_ID=$row['Assignment_ID'] ORDER BY Date DESC LIMIT 10;

SELECT * from tmpSubquery ORDER BY Date ASC;

DROP TEMPORARY TABLE tmpSubquery;

There are only two things to remember to make this work. 1) All of these statements must be on the same, continuously connected connection. You cannot close the connection between the statements. 2) If you do not drop the temp table and you try to execute this set of commands on the same continuous connection, you will get an error.

Shawn Green Database Administrator Unimin Corporation - Spruce Pine