List:General Discussion« Previous MessageNext Message »
From:Alex Gemmell Date:November 30 2005 4:48pm
Subject:Re: Rewriting subquery for old MySQL
View as plain text  
rouvas wrote:
> On Wednesday 30 November 2005 18:11, Alex Gemmell wrote:
> 
>>SGreen@stripped wrote:
>>
>>>Alex Gemmell <agemmell@stripped> 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
>>
>>Thanks Shawn but I couldn't seem to get this to work.
>>
>>I made the point of checking the MySQL User that PHP connects with.  I
>>gave it CREATE and DROP privilages on the database schema in question.
>>It previously only had SELECT privilages.
>>
>>Here's my PHP code to show you how I tried it:
>>
>>[PHP]
>>$query = "CREATE TEMPORARY TABLE tmpSubquery
>>	SELECT * FROM tblactivities
>>	WHERE Assignment_ID='".$row['Assignment_ID']."'
>>	ORDER BY Date DESC LIMIT 10;
>>	SELECT * FROM tmpSubquery ORDER BY Date ASC;
>>	DROP TEMPORARY TABLE tmpSubquery;";
>>
>>$result_activities = mysql_query($query);
>>[/PHP]
>>
>>I tried splitting up the queries to see if it helped but it didn't work
>>either:
>>
>>[PHP]
>>$query1 = "CREATE TEMPORARY TABLE tmpSubquery
>>	SELECT * FROM tblactivities
>>	WHERE Assignment_ID='".$row['Assignment_ID']."'
>>	ORDER BY Date DESC LIMIT 10;";
>>mysql_query($query1);
>>
>>query2 = "SELECT * FROM tmpSubquery ORDER BY Date ASC;";
>>$result_activities = mysql_query($query2);
>>
>>query3 = "DROP TEMPORARY TABLE tmpSubquery;";
>>mysql_query($query3);
>>[/PHP]
>>
>>Perhaps I am missing something obvious here?  I hope you or anyone else
>>can offer some more help.
>>
>>Alex
> 
> 
> Hi Alex,
> 
> a ";" after the tmpSubquery above would be nice...:-)
> 
> -Stathis

Actually, not a ";" but brackets around the SELECT statement were missing.

Also, I needed to give the User PHP was using to connect to MySQL the 
CREATE_TMP_TABLE privilage - obviously...  :0)

So here's the final bit of code in PHP.  I needed to split up the 
queries as they wouldn't work in one go, but as Shawn pointed out this 
all must happen on the same connection thread to MySQL:

[PHP]
$query1 = "CREATE TEMPORARY TABLE tmpSubquery
	(SELECT * FROM tblactivities
	 WHERE Assignment_ID='".$row['Assignment_ID']."'
	 ORDER BY Date DESC LIMIT 10);";
mysql_query($query1);

$query2 = "SELECT * FROM tmpSubquery ORDER BY Date ASC;";
$result_activities = mysql_query($query2);

$query3 = "DROP TEMPORARY TABLE tmpSubquery;";
mysql_query($query3);
[/PHP]

Thanks for the SQL Shawn!
Thread
Rewriting subquery for old MySQLAlex Gemmell30 Nov
  • Re: Rewriting subquery for old MySQLSGreen30 Nov
    • Re: Rewriting subquery for old MySQLAlex Gemmell30 Nov
      • Re: Rewriting subquery for old MySQLrouvas30 Nov
        • Re: Rewriting subquery for old MySQLAlex Gemmell30 Nov
        • Re: Rewriting subquery for old MySQLAlex Gemmell30 Nov
        • Re: Rewriting subquery for old MySQLSGreen30 Nov
      • Re: Rewriting subquery for old MySQLDan Nelson30 Nov
        • Re: Rewriting subquery for old MySQLAlex Gemmell30 Nov
          • Re: Rewriting subquery for old MySQLMichael Stassen30 Nov
      • Re: Rewriting subquery for old MySQLMichael Stassen30 Nov
        • Re: Rewriting subquery for old MySQL - SOLVEDAlex Gemmell30 Nov
          • Re: Rewriting subquery for old MySQL - SOLVED - correction!Alex Gemmell6 Dec