selecting from from one table rows that arent in other table

[Anonymous]

This may be a stupid question:

I got 2 tables, users and results and I want to select the rows in users where the attribute id is not in any other row in results. In Oracle it would be something like:

select id from users
where id not in
(select user_id from results);

In MySQL doesnt work!!! 8O

Why? How can I do this please?

 

[gesf]

It´s almost the same thing

You can use both:

SELECT user_fields FROM users
WHERE NOT EXISTS (SELECT * FROM results WHERE users.id = results.id);

or

SELECT user_fields FROM users
WHERE id NOT IN (SELECT id FROM results);

edited:
This subselects only works with mysql prior 4.1.

 

[Anonymous]

Does not work!!!!!!! :evil: :?

Do I need a special version(like greater than 4) to use sub-queries?

8O

 

[gesf]

Sorry! Actually i made a mistake. This kind of subselects works with MySQL prior to 4.1.

It should work! Is your MySQL prior to 4.1 ?

Anyway, try this:

SELECT users.id, results.user_id FROM users, results
WHERE users.id <> results.user_id;