SQL Query to find users not having specific group id in join table

Question

I have a table named jos_user_usergroup_map that has 2 columns: user_id and group_id

A user can be a member of several groups and thus has several rows in this table, each with the group ID, e.g.

user_id|group_id
62     | 1
62     | 4
62     | 12
108    | 1

I want to find all the user_id's where that is not having a group_id = 12, but it's giving me a headache...

In the above example, I should find only user_id = 108

Any ideas how to do this? Any help is appreciated.

Thanks

Answer 1

Try this:

SELECT DISTINCT A.user_id
FROM jos_user_usergroup_map A 
LEFT OUTER JOIN jos_user_usergroup_map B ON A.user_id = B.user_id AND B.group_id = 12 
WHERE B.user_id IS NULL;

Answer 2

Use post aggregation filtering

SELECT user_id FROM jos_user_usergroup_map 
GROUP BY user_id
HAVING SUM(group_id=12)=0

Answer 3

I would try something like:

SELECT DISTINCT `user_id`
FROM `jos_user_usergroup_map`
WHERE `user_id` NOT IN (
    SELECT `user_id` 
    FROM `jos_user_usergroup_map` 
    WHERE `group_id` = 12
)

Or using exists (Which is probably faster than in):

SELECT DISTINCT `user_id`
FROM `jos_user_usergroup_map`
WHERE NOT EXISTS (
    SELECT `user_id` 
    FROM `jos_user_usergroup_map` 
    WHERE `group_id` = 12 AND `user_id` = `jos_user_usergroup_map`.`user_id`
)

However, please note that whilst these solutions are logically easy to understand, the JOIN based answers are normally much faster in practice.