List:General Discussion« Previous MessageNext Message »
From:Alex Gemmell Date:November 30 2005 4:11pm
Subject:Re: Rewriting subquery for old MySQL
View as plain text  
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
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