Search code examples
sqlrelational-division

Need simplify sql query with "count" function and "IN" operator

I have a table only consisting of two columns: 

ObjectID||PropertyID

The task: get the all ObjectID whith PropertyID == (P1 and P2 and P3 and ...).

I solved this task:

  SELECT *
  FROM  ( 
        SELECT SD.ObjectID ObjectID,
               count( SD.ObjectID )  countMatchingProperties
          FROM table AS SD
         WHERE SD.PropertyID IN ( P1, P2, P3, ..., Pn ) 
         GROUP BY ObjectID 
    ) 
    AS C
 WHERE C.countMatchingProperties > n-1

But I did not leave a thought that this task can be solved easier and faster.

Solution 
  • select ObjectID 
from table 
where PropertyID in (P1, P2, P3, P4)
group by ObjectID
having count(distinct PropertyID) = 4

    The count (e.g., 4) must match the number of unique values in your IN clause.