Join to find rows not in second table

snoop · #1 (**permalink**) 01-18-06, 18:37

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??

tombell · #2 (**permalink**) 01-19-06, 09:28

You need a left join to do that
without a subquery:

Code:

SELECT group_id
FROM group g
LEFT JOIN usergroup u
ON g.group_id=u.group_id
AND u.user_id=3
WHERE u.user_id IS NULL

tombell · #3 (**permalink**) 01-19-06, 09:34

Quote:

Originally Posted by snoop

Hi,
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

)

Additionally, in the above query you don't need the join:

Code:

SELECT group_id FROM group
WHERE group_id NOT IN (
SELECT group_id FROM usergroup WHERE user_id=3 GROUP BY group_id
)

snoop · #4 (**permalink**) 01-19-06, 09:48

Excellent stuff, works perfectly. Appreciate the help.

Internet Services

Web Development

Digital Marketing

Consumer Tech