I'm having some trouble figuring out how to write the proper query after doing a JOIN. I need to get all users in Group 1 while excluding a subset of these results.
Table users:
id name
1 John Smith
2 Joe Blow
3 Mary Jane
Table users_groups:
user_id group_id
1 1
1 3
1 4
2 1
2 4
2 5
3 1
3 6
Everyone in Group 6 will also be in Group 1, however, not everyone in Group 1 will be in Group 6. In other words, Group 6 is a sub-set of Group 1.
I need a query that will give me a list of all users who are in Group 1 (while excluding the users in Group 6). For the example above, I should get two results, John Smith and Joe Blow.
I'm using CodeIgniter v3
Here is my attempt (I removed the cache code for clarity)...
$this->db->from('users');
$this->db->select('
users.id AS `id`,
users.name AS `name`,
users_groups.group_id AS `group_id`
', FALSE);
$this->db->join('users_groups', 'users_groups.user_id = users.id', 'LEFT');
$this->db->group_by('users.email'); // remove duplication caused by JOIN
$this->db->where('users_groups.group_id = 1'); // get all users in Group 1
$this->db->where('users_groups.group_id <> 6'); // ignore all users in Group 6
return $this->db->get()->result_array();
The problem I'm having here is that I always get the full list of users in Group 1. Because the JOIN produces a list of all users and all groups, where the same user is listed multiple times, one entry for every Group that person belongs. My query is removing the Group 6 entries, but this is no good since the same users are also in Group 1.
I just explained why my query is failing, but I still cannot figure out how to achieve success. How do I get the Group 1 users and then remove the subset of users that are in Groups 1 & 6? These users can also be in other Groups, but these should be ignored... I just want to exclude users who are in Groups 1 & 6 from the list of users in Group 1.
Each user in the result:
- must be in Group 1
- must not be in Group 6
- may or may not be in any other Group
Any suggestions appreciated.
