Excluding information from results + syntax issue

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?

GregoryD

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

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)

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

On Wed, 07 May 2008 22:20:28 +0200, cvh@LE
<christian.hansel@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:
>> > 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).

>> > 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);
>
> 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)

I second that, but I detest USING statements. Being more explicit (LEFT
JOIN opt o ON o.id = u.id) would have my preference.
--
Rik Wasmus