CODEWITHSUNDEEP
sqlselect
monto
monto

Reputation: 61

SQL: Selecting User IDs, and excluding all the User IDs if one entry has a certain value

My database

Hi,

Looking at the picture I've attached. I'm trying to select all the User IDs which do not have a Role ID of 1. So I want the query to return User IDs 6, 13, and 15. However, when I use a query for this it will also return User ID 5, as it has 2 entries, with one entry having a Role ID of 3. Is there anyway to exclude UserID 5 due to its associaiton with having a RoleID of 1?

SELECT UserID
FROM UserTable (example)
WHERE RoleID <> 1

Thanks.

Upvotes: 1

Views: 1158

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

If you only want userIds, then use group by and having:

SELECT UserID
FROM UserTable 
GROUP BY UserId
HAVING SUM(CASE WHEN RoleID = 1 THEN 1 ELSE 0 END) = 0;

This selects each (appropriate) UserId once -- which seems to be what you are asking for.

The HAVING clause is counting the number of roles that are "1". The = 0 says that there none exist.

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use not exists

select t.UserID from UserTable  t
where not exists ( select 1 from UserTable t1 
                   where t1.Userid=t.userid and Roleid=1
                  )

Upvotes: 2

Amin Mozhgani
Amin Mozhgani

Reputation: 604

SELECT UserID
FROM UserTable (example)
WHERE UserID not in (SELECT UserID FROM UserTable (example) WHERE RoleID = 1)

First of all we select all the users with RoleID = 1 and we select their userIDs, and then we select all the users that are not in the first list.

Upvotes: 3

Related Questions