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.
id name 1 John Smith 2 Joe Blow 3 Mary Jane
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
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.