"not in" in mysql 3.23

[mschultem]

hi,
i have the following working query in mysql 5:
$query="SELECT demography.email, invited.pid FROM demography, invited WHERE demography.pid=invited.pid AND invited.finishedExp = '1' AND invited.experiment_id='$ExperimentID' AND invited.pid NOT IN (SELECT distinct payed.pid from payed WHERE payed.experiment_id='$ExperimentID')";

after a provider change i have only mysql 3.23 available. could this be the reason the above query does not work any more? especiall then "NOT IN" at the end? is there a way to work around that?

thanks
m

[bcolflesh]

Subselects are not supported in 3.23 - I think they started in 4.1x, but you should check the MySQL site notes for sure.

You can create a temp table with your initial results and then a secondary query to get your required result set.

[mschultem]

thanks for your reply!

ok i create a temp query like:
$tempquery="CREATE TEMPORARY TABLE MyTemporaryTable SELECT distinct payed.pid from payed WHERE payed.experiment_id='$ExperimentID'";

which i have to get to the server somehow in php:
$tempresult=mysql_query($tempquery);

but how do i query MyTemporaryTable then?

$query="SELECT demography.email, invited.pid FROM demography, invited WHERE demography.pid=invited.pid AND invited.finishedExp = '1' AND invited.experiment_id='$ExperimentID' AND invited.pid NOT IN MyTemporaryTable)";

does not work ...
m

[bcolflesh]

You want to JOIN on the results of the temp table - take a look at:

[dev.mysql.com...]