Join to find rows not in second table

[snoopdd]

Hi,

I'm trying to write a SELECT for MySQL 4.0 using a JOIN. I can get it to work in v4.1 using a subquery, but my ISP provides v4.0 only.

I've got 2 tables:
- group: Describes groups that exist, key is group_id
- usergroup: Members of groups - has user_id and group_id

I want to find which groups a user **doesn't** belong to (say user_id=3).

In MySQL 4.1+ I can do this using a subquery:

SELECT group_id FROM group
WHERE group_id NOT IN (
SELECT group_id
FROM group g,usergroup ug
WHERE ug.group_id = g.group_id
AND ug.user_id = 3
)

This query doesn't work in MySQL 4.0, no I need to use JOIN (I think). I've been searching forums and trying things out, but I cannot figure out how to make it work.

Can anyone help please??

 

[swampBoogie]

SELECT group_id 
  FROM group g 
  left join usergroup ug
  on ug.group_id = g.group_id
   and ug.user_id = 3
where ug.group_id is null

 

[snoopdd]

Brilliant, works like a bought one.

Thanks for that, your're a champ.