On Wed, 07 May 2008 18:11:48 +0200, GregoryD <deergregoryd@gmail.com>
wrote:

> We are currently running on mysql 4.0-27 at my business. I have what
> should
> seem to be a basic query, but am having trouble with syntax errors and
> can't
> figure out why.
>
> I have two tables (there's more info in each table, but I doubt they're
> germaine to the discussion):
>
> users
>
> +---------+-------------+--------------------+
> | id | first_name | email |
> +---------+-------------+--------------------+
> | 1 | Bob | blah@blah.org |
> | 2 | Fred | fred@fred.org |
> | 3 | Mark | mark@mark.com |
> | 4 | Dave | dave@dave.com |
> +---------+-------------+--------------------+
>
> and
>
> opt
>
> +-----+
> | id |
> +-----+
> | 2 |
> | 4 |
> +---- +
>
>
> I am trying to select all the records in users whose ids do not exist in
> opt. The ids in both tables are unsigned INTEGER(10). In other words,
> the
> results should be:
>
> +---------+-------------+--------------------+
> | id | first_name | email |
> +---------+-------------+--------------------+
> | 1 | Bob | blah@blah.org |
> | 3 | Mark | mark@mark.com |
> +---------+-------------+--------------------+
>
> I have tried the following (not so much the right query as trying to
> figure
> out why none of these queries work on the server):
>
> SELECT * FROM users WHERE id <> ANY (SELECT id FROM opt);
>
> SELECT * FROM users WHERE id IN (SELECT id FROM opt);
>
> SELECT * FROM users WHERE users.id IN (SELECT id FROM opt);
>
> In each case, I get a syntax error. What exactly am I doing wrong?

None of those give me a syntax error, and these two work if you just ad
NOT:

SELECT * FROM users WHERE id NOT IN (SELECT id FROM opt);
SELECT * FROM users WHERE users.id NOT IN (SELECT id FROM opt);
--
Rik Wasmus