compare two tables question

Anon

I have a problem with what seems a simple recursive query........

I have to compare two tables to check for email addys in one table that are NOT in the other. The emails to check against are in the table named table13, and have TIMECODES in them, this way the script doesn't compare EVERY email in the table.

To get the initial email addresses I use the following...

// GETS EMAIL ADDRESSES TO COMPARE
<?
$sql = "SELECT EMAIL
FROM table13
WHERE TIMEREGISTERED > 0
";

$result = @($sql,$connection) or die ("Couldn't execute query one.");

// CHECK FOR RESULTS

if (mysql_num_rows($result) > 0) {
while ($Row = mysql_fetch_array($result, 1)) {

// SET RESULT TO A VARIABLE NAME

$table13email = $newRow[EMAIL];

Now that I have set the "searchable" email as a variable name, how do I search my other table (table1.EMAIL) and have the browser only list emails from table13 that DO NOT have a match in table1??

Thank you!

Kev

rrijnders

Should be able to do that with a simple query:

SELECT EMAIL from table13
WHERE (TIMEREGISTERED > 0) AND
EMAIL NOT IN (SELECT EMAIL FROM table1)

I don't know about mySQL but it works perfectly in postgreSQL...

-- Rich

Anon

MySQL doesn't seem to be able to handle this query. It would sure be nice if it could! Does anyone else have any suggestions on how to proceed?

Thanks,

Kev

Anon

mysql doesn't completely support sub-queries but you can rewrite the query and have it work (I'll call the tables email1 and email2):

select email1.* from email1 left join email2 on email1.email = email2.email
where
email2.id is null
and
email1.autoregistered > 0;

Look up the syntax of left join. It returns all matches, and when the second table has no values for a row they are returned as null. By adding the 'is null' we restrict the results to only email id's from 1 that do not exist in 2.

Anon

Thanks Jon! That did the trick! Good thing too......every other join method I was trying would just bog the server down to where MySQL needed to be restarted.

Thanks again!