Filter coming result in same column

Pointid	Productid
5	24
6	24
6	25
6	26
7	24
7	25
7	26
7	27

For the query:

select distinct pointid
from mytable
where productid in (24,25,26)

The result is: 5,6,7

But I want only 6. No more No less..

How can I do it?

Answer

The main goal is to find those pointid where their productid are EXACTLY 24, 25, 26. Importantly: no more, no less.

In essence, you are requiring two distinct conditions:

• 1. That the pointid contains ALL of the following productids; 24, 25, 26, and,
• 2. That the pointid does not contain any other productid.

Both conditions can be addressed with a having clause.

• 1. You make sure that it contains all of the productids. As there are 3, you want the count of the productids that match 24, 25, 26, to be exactly 3: COUNT(DISTINCT CASE WHEN productid in (24, 25, 26) THEN productid END) = 3.
• 2. You want it to contain no other productid. So you make sure that the total value of distinct productids is 3: COUNT(DISTINCT productid) = 3.

The following query puts it all together:

SELECT pointid
FROM mytable
GROUP BY pointid
HAVING COUNT(DISTINCT CASE WHEN productid in (24, 25, 26) THEN productid END) = 3
  AND COUNT(DISTINCT productid) = 3
;

If you wanted to do something similar with different conditions (as you mentioned in your comments):

• Replace 24,25,26, for the productids that you want to check.
• Replace 3 for the number of productids that you are checking.