Select results with only 2 types of products-CodePudding

I need to receive claims (of a customer) who has only specific types of products (productid IN(1,2)). There are different other products but I need only the ones with type 1 and 2 (who would have both at the same time). Client might have other products as well but important to take the ones who have 1&2

Example

clientID claimID productID
 123       333      1
 123       333      2
 123       333      4
 123       333      6
 123       333      7

CodePudding user response：

SELECT C.clientID
FROM YOUR_TABLE AS C
WHERE C.productID IN(1,2)
GROUP BY C.clientID
HAVING COUNT(DISTINCT C.productID)=2

CodePudding user response：

The simplest (not necessarily prettiest) way to do this is:

SELECT c1.*
FROM claims c1
WHERE EXISTS (
    SELECT c2.*
    FROM claims c2
    WHERE c2.productID IN (1, 2)
    AND c2.clientID = c1.clientID
)

In other words, select each claim c1 where there is at least one claim c2 that has the same clientID as c1, and a productID of 1 or 2.