Excluding information from results + syntax issue

#1

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

+---------+-------------+--------------------+

Quote:

id | first_name | email |
+---------+-------------+--------------------+
1 | Bob | blah (AT) blah (DOT) org |
2 | Fred | fred (AT) fred (DOT) org |
3 | Mark | mark (AT) mark (DOT) com |
4 | Dave | dave (AT) dave (DOT) com |
+---------+-------------+--------------------+

and

opt

+-----+

Quote:

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:

+---------+-------------+--------------------+

Quote:

id | first_name | email |
+---------+-------------+--------------------+
1 | Bob | blah (AT) blah (DOT) org |
3 | Mark | mark (AT) mark (DOT) 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

#2

On Wed, 07 May 2008 18:11:48 +0200, GregoryD <deergregoryd (AT) gmail (DOT) com>
wrote:

Quote:

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 (AT) blah (DOT) org |
| 2 | Fred | fred (AT) fred (DOT) org |
| 3 | Mark | mark (AT) mark (DOT) com |
| 4 | Dave | dave (AT) dave (DOT) 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 (AT) blah (DOT) org |
| 3 | Mark | mark (AT) mark (DOT) 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

#3

On May 7, 6:16*pm, "Rik Wasmus" <luiheidsgoe... (AT) hotmail (DOT) com> wrote:

Quote:

On Wed, 07 May 2008 18:11:48 +0200, GregoryD <deergrego... (AT) gmail (DOT) 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... (AT) blah (DOT) org * * * *|
| * * * 2 * * * | * Fred * * * * *| f... (AT) fred (DOT) org * * * * |
| * * * 3 * * * | * Mark * * * * | m... (AT) mark (DOT) com * *|
| * * * 4 * * * | * Dave * * * * *| d... (AT) dave (DOT) 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... (AT) blah (DOT) org * * * *|
| * * * 3 * * * | * Mark * * * * | m... (AT) mark (DOT) 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)

#4

On May 7, 10:20*pm, "cvh@LE" <christian.han... (AT) cpi-service (DOT) com> wrote:

Quote:

On May 7, 6:16*pm, "Rik Wasmus" <luiheidsgoe... (AT) hotmail (DOT) com> wrote:

On Wed, 07 May 2008 18:11:48 +0200, GregoryD <deergrego... (AT) gmail (DOT) 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... (AT) blah (DOT) org * * * *|
| * * * 2 * * * | * Fred * * * * *| f... (AT) fred (DOT) org * * * * |
| * * * 3 * * * | * Mark * * * * | m... (AT) mark (DOT) com* *|
| * * * 4 * * * | * Dave * * * * *| d... (AT) dave (DOT) 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... (AT) blah (DOT) org * * * *|
| * * * 3 * * * | * Mark * * * * | m... (AT) mark (DOT) 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

#5

On Wed, 07 May 2008 22:20:28 +0200, cvh@LE
<christian.hansel (AT) cpi-service (DOT) com> wrote:

Quote:

On May 7, 6:16*pm, "Rik Wasmus" <luiheidsgoe... (AT) hotmail (DOT) com> wrote:
On Wed, 07 May 2008 18:11:48 +0200, GregoryD <deergrego... (AT) gmail (DOT) 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Excluding information from results + syntax issue

comp.databases.mysql comp.databases.mysql

Excluding information from results + syntax issue - 05-07-2008 , 11:11 AM

Re: Excluding information from results + syntax issue - 05-07-2008 , 11:16 AM

Re: Excluding information from results + syntax issue - 05-07-2008 , 03:20 PM

Re: Excluding information from results + syntax issue - 05-07-2008 , 03:26 PM

Re: Excluding information from results + syntax issue - 05-07-2008 , 08:10 PM