sql question on 'not in'-operator

[timv]

i have two table: groups and groups_invisible

table def+data: groups_invisible
+---------+----------+
| user_id | group_id |
+---------+----------+
| 100 | 4 |
| 100 | 5 |
| 100 | 6 |
+---------+----------+

table def+data: groups
+----------+---------------+
| group_id | group_name |
+----------+---------------+
| 2 | leverancier |
| 3 | klant |
| 4 | claim |
| 5 | sample |
| 6 | communication |
+----------+---------------+


Now, my query is
SELECT groups.group_id , group_name FROM alba03.groups
WHERE groups.group_id not in (select group_id from alba03.groups_invisible where user_id=100)

but mysql gives an error at the first '('

These queries do work:
1. SELECT groups.group_id , group_name FROM alba03.groups
WHERE groups.group_id not in (4,5,6)
--> returning (1,2,3)

2. select group_id from alba03.groups_invisible where user_id=100
--> returning (4,5,6)

So both parts of the query seem to work but the query itself not...

What did i do wrong?

[timv]

****,
it probably has to do with: http://www.dbforums.com/t1081939.html

(mysql version < 4.1 does not handle subqueries )

[timv]

all right,
i couldn't solve this rewriting the query.

what i want is a result op couples (group_id, group_name, visible) with all different group_ids. Visible being 0 if the group_id occurs for a certain user (let's say user '100'), 1 otherwise.
Watch out, groups_invisible can also have couples for other users, e.g. (40, 4) for user '40'.

**** i can't solve it without using subqueries and 'exists'.
Can someone?


table def+data: groups_invisible
+---------+----------+
| user_id | group_id |
+---------+----------+
| 100 | 4 |
| 100 | 5 |
| 100 | 6 |
+---------+----------+

table def+data: groups
+----------+---------------+
| group_id | group_name |
+----------+---------------+
| 2 | leverancier |
| 3 | klant |
| 4 | claim |
| 5 | sample |
| 6 | communication |
+----------+---------------+