[Resolved] SQL JOIN Not Working

[earthlingzed]

I am trying to query two tables (test and mail sent) at the same time with a JOIN and having trouble. I want to grab all email addresses in the test table 10 at a time that are not already in teh mail_sent table. The following statement returns nothing.

$sql = "SELECT * FROM test INNER JOIN mail_sent ON test.email IS NOT NULL AND test.email <> '' AND test.email <> mail_sent.email LIMIT 10";

Where am I going wrong?

[pohopo]

For this I believe you need a not in..

$sql = "SELECT * FROM test WHERE test.email NOT IN (SELECT mail_sent.email FROM mail_sent) LIMIT 10"

This is so it is easier to read...

SELECT *
FROM test
WHERE test.email NOT IN
(
SELECT mail_sent.email
FROM mail_sent
)
LIMIT 10

[earthlingzed]

That seems pretty logical to me, but I am still getting some errors from that. I go this error:

Query failed: You have an error in your SQL syntax near 'SELECT mail_sent.email FROM mail_sent) LIMIT 10' at line 1 SQL: SELECT * FROM test WHERE test.email NOT IN (SELECT mail_sent.email FROM mail_sent) LIMIT 10 in /home/jweasel/soupstudios-www/richemail/classes/MySQL.php on line 113

It does not like the nested SELECT, so I tried this too:

$sql = "SELECT * FROM test WHERE test.email NOT IN (mail_sent.email) LIMIT 10";

This gave me no error, but it also returned no data when it should have.

Any ideas anyone?

[pohopo]

Okay, it looks like what I gave you was wrong. Well, it would be right in Oracle, Sybase, and MSSQL, but it does not seem to work in MYSQL. Here is what I believe should work..

$sql = "SELECT test.email FROM test LEFT JOIN mail_sent ON test.email = mail_sent.email WHERE mail_sent.email IS NULL"

[earthlingzed]

Many thanks.
That works!!