Need UBER fast mysql help

JonD · 13 Oct 2006 at 18:57

I have this query which works fine on most servers. But the host has mysql version: 3.23.58, which I dont think supports nested queries.

So how can I fix this query to not use a nested query?

Code:

$query = mysql_query("SELECT * FROM ".TABLE_PREFIX."roster
WHERE rosterid IN (SELECT rosterid FROM ".TABLE_PREFIX."team)
ORDER BY name ASC");

Thx
jd

JonC · 13 Oct 2006 at 19:37

try EXISTS instead of IN.

Try:

Code:

$query = mysql_query("SELECT * FROM ".TABLE_PREFIX."roster a
WHERE EXISTS (SELECT rosterid FROM ".TABLE_PREFIX."team b WHERE a.rosterid = b.rosterid)
ORDER BY rosterid");

Although that's DB2 stylee, it may work.....

JonD · 13 Oct 2006 at 19:48

That uses a nested query, which I cant use on thier version.

Is there anyway I can update the mysql version of does the provider have to do that?

arty · 13 Oct 2006 at 19:52

Does this work?

Code:

SELECT r.* FROM ".TABLE_PREFIX."roster AS r
INNER JOIN ".TABLE_PREFIX."team AS t ON r.rosterid = t.rosterid
GROUP BY r.*
ORDER BY r.name ASC

The GROUP BY is a bit dodgy - you may need to list all of the field names in roster for it to work.

arty

JonD · 13 Oct 2006 at 20:06

arty said:
Does this work?

Code:

SELECT r.* FROM ".TABLE_PREFIX."roster AS r INNER JOIN ".TABLE_PREFIX."team AS t ON r.rosterid = t.rosterid GROUP BY r.* ORDER BY r.name ASC

The GROUP BY is a bit dodgy - you may need to list all of the field names in roster for it to work.

arty

Yeh that seems to work fine atm, I replaced the * with the fields I wanted.

Thx

Ill let you know if it fails!