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

Question

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.

the table has two rows? or you mean two columns? – Forte L. Jul 10 '12 at 14:01 — Forte L., Jul 10 '12 at 14:01

score 1 · Answer 1 · answered Jul 10 '12 at 14:01

1

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

answered Jul 10 '12 at 14:01

Razvan

9,925
6
38
51

score 0 · Accepted Answer · answered Jul 10 '12 at 14:03

0

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.

answered Jul 10 '12 at 14:03

D'Arcy Rittich

167,292
40
290
283

I want hear explanation too: checked both answers and they work correct but i choose use this because it is shorter – Mitrodan Jul 14 '12 at 12:35

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

2 Answers2