CODEWITHSUNDEEP
mysqlsql
Ben
Ben

Reputation: 9001

SQL query to find users with certain values in another table

Overview

I have two tables - tmc_users and tmc_user_usergroup_map.

In tmc_users, there is a list of user_ids and in tmc_usergroup_map each user_id can be associated with multiple group_ids.

I am trying to find all the user_ids who have a group_id of 12 but do not have a group_id of 17.

Each user can be a member of both.

Current query

This is the query to get all the users associated with the group_id 12, which returns a total number of 1439 rows:

SELECT `id`, LTRIM(RTRIM(`name`)), COUNT(`id`) AS `total`
    FROM `tmc_users` u
    LEFT JOIN `tmc_user_usergroup_map` g
    ON u.`id` = g.`user_id`
    WHERE g.`group_id` = 12
    ORDER BY LTRIM(RTRIM(`name`)) ASC

New query

This is what I've tried in order to filter out users who also are in the group_id 17:

SELECT `id`, LTRIM(RTRIM(`name`)), COUNT(`id`) AS `total`
    FROM `tmc_users` u
    LEFT JOIN `tmc_user_usergroup_map` g
    ON u.`id` = g.`user_id`
    WHERE g.`group_id` != 17
    AND g.`group_id` = 12
    ORDER BY LTRIM(RTRIM(`name`)) ASC

However, this returns the same number of rows - 1439 - whereas the actual number of users this should have is 1353.

How can I make this work in one single query?

Upvotes: 0

Views: 63

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use NOT EXISTS:

SELECT `id`, LTRIM(RTRIM(`name`)), COUNT(`id`) AS `total`
FROM `tmc_users` u
LEFT JOIN `tmc_user_usergroup_map` g
  ON u.`id` = g.`user_id`
WHERE g.`group_id` = 12 AND 
     NOT EXISTS (SELECT 1
                 FROM `tmc_user_usergroup_map` AS t
                 WHERE t.`user_id` = u.`id` AND t.`group_id` = 17)
ORDER BY LTRIM(RTRIM(`name`)) ASC

Upvotes: 1

Related Questions