On May 7, 10:20*pm, "cvh@LE" <christian.han...@cpi-service.com> wrote:
> 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)

furthermore after rereading your post I saw that you are running
version 4.0.which to my knowledge doesnt support subselects