On May 7, 6:16*pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Wed, 07 May 2008 18:11:48 +0200, GregoryD <deergrego...@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 * * * * * | b...@blah.org * * * *|
> > | * * * 2 * * * | * Fred * * * * *| f...@fred.org * * * * |
> > | * * * 3 * * * | * Mark * * * * | m...@mark.com * *|
> > | * * * 4 * * * | * Dave * * * * *| d...@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 * * * * * | b...@blah.org * * * *|
> > | * * * 3 * * * | * Mark * * * * | m...@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

While Rik's solution is valid and does work I would rather opt for a
solution based on joins since these usually are more efficient than
subselects

Select u.* FROM users u left join opt o using(id) where isnull(o.id)