another wrong bit of SQL

dugawug

hi, hoping to find a bit of advice.
just trying to get people out of one table who have a password and who ARE NOT in another table, and yes, the tables have relational ID's.

see this SQL...why does MySQL not like it??:

SELECT Contacts.* FROM Contacts WHERE LENGTH(Contacts.Password) > 0 AND ContactID NOT IN (SELECT Users.ContactID FROM Users);

eoghain

Because MySQL doesn't support sub-queries. Which is what you are using. Try this:

SELECT Contacts.*
  FROM Contacts
  LEFT JOIN Users
    ON (Contacts.ContactID = Users.ContactID)
 WHERE LENGTH(Contacts.Password) > 0
   AND Users.ContactID IS NULL;

It's not guaranteed to work, but that is the path you need to go down.

pete_bisby

What version of MySQL are you using? Anything below verison 4.1 will not accept subqueries.

eoghain

Guess I haven't been keeping up on my MySQL releases, I thought they hadn't released a version with Sub-Queries yet.

dugawug

AHHH!
ok, i'm on 4.0.20 so no subqueries for me!
thanks anyhow, the re-work worked perfectly!
you rock!