mysql select all records where ColumnA = X but ColumnA has no other value-CodePudding

I have a usersTable with a data set like the following:

 -------------------- ------ 
| user_ID            | ceID |
 -------------------- ------ 
| 20201011_557141020 | 1136 |
| 20201118_936245153 | 1113 |
| 20200224_856349817 | 1113 |
| 20201021_979024023 | 1139 |
| 20201021_979024023 | 1146 |
| 20201104_500741407 | 1139 |
| 20201021_979024023 | 1147 |
| 20201021_979024023 | 1113 |
| 20210412_238528636 | 1118 |
| 20220523_576943860 | 1113 |
| 20220523_576943860 | 1119 |
| 20220522_893258175 | 1119 |
 -------------------- ------

I need to select all users where ceID=1113 and do not have any other entries where ceID equals another value.

All users where ceID=1113:

 -------------------- ------ 
| user_ID            | ceID |
 -------------------- ------ 
| 20201118_936245153 | 1113 |
| 20200224_856349817 | 1113 |
| 20201021_979024023 | 1113 |
| 20220523_576943860 | 1113 |
 -------------------- ------

But the desired results should be:

 -------------------- ------ 
| user_ID            | ceID |
 -------------------- ------ 
| 20201118_936245153 | 1113 |
| 20200224_856349817 | 1113 |
 -------------------- ------

Because users 20201021_979024023 and 20220523_576943860 do have other entries where ceID does equal something else.

I have tried this, which isn't working, and don't understand why nor I have I been able to think of another query to do this:

 select user_ID from clubUsers where ceID=1113 and not exists (select user_ID clubUsers where ceID != 1113) ;

CodePudding user response：

Aggregation is one straightforward option:

SELECT user_ID
FROM clubUsers
GROUP BY user_ID
HAVING MIN(ceID) = MAX(ceID)  -- min/max ceID the same => only 1 value
       AND MIN(ceID) = 1113;  -- that single value is 1113

CodePudding user response：

Hello please try this one:

SELECT T1.user_id FROM
(select user_ID, COUNT(user_ID)
from clubUsers
where ceID=1113
GROUP BY user_ID) AS T1 
INNER JOIN(select user_ID, COUNT(user_ID) from clubUsers GROUP BY user_ID HAVING COUNT(user_ID) = 1) AS T2
    ON T1.user_ID = T2.user_ID;

If we execute the query then: