CODEWITHSUNDEEP
mysqlsql
Fabio
Fabio

Reputation: 245

SQL Select users with condition verified in multiple rows

I have the following table:

table services-users

the first column contains Services_ID, the second Users_ID and the third indicates if the user is enabled for the service. I need to identify (one time for user) which users are allowed for both services with ID 4 and 5.

I wrote that query with Mysql:

SELECT DISTINCT User 
  FROM tablexxx
 WHERE Service IN (4, 5)
   AND User NOT IN (SELECT User
                      FROM tablexxx
                     WHERE Service IN (4, 5)
                       AND Active = FALSE);

That query works, but, is there a better technical way to write it?

The right result is:

Result

Upvotes: 1

Views: 1592

Answers (1)

juergen d
juergen d

Reputation: 204766

select user
from your_table
where service in (4,5)
and active = TRUE
group by user
having count(distinct service) = 2

or

select user
from your_table
group by user
having sum(active = TRUE and service = 4) > 0
   and sum(active = TRUE and service = 5) > 0

Upvotes: 3

Related Questions