CodeIgniter query to exclude a subset from results

Question

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.

Answer 1

You need a "not exists" clause in there as a filter.

And not exists (select 1 from users_groups x where 
x.user_id = users_groups.user_id and group_id = 6

Im not familiar with code ignite but im sure this is doable

Answer 2

Thanks to Philip's answer, it's working. This is how to do it within CodeIgniter...

$this->db->where('users_groups.group_id = 1'); // get all users in Group 1

$this->db->where('
    NOT EXISTS (
        SELECT 1 FROM users_groups x 
        WHERE x.user_id = users_groups.user_id AND group_id = 6
    )
');  // exclude users in Group 6