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