MySQL result Group by two values

I need some help for a query to group some rows, I’m trying the whole day and find no solution and I’m sure it’s easy. Maybe some one can bring me light in the dark:

My Table:

id | Bid | Cid | value
 4 | 22  | 11  | 33
 5 | 24  | 11  | 33
 6 | 25  | 11  | 33
 7 | 24  | 11  | 100
 8 | 25  | 16  | 150

I want only the result Bid=25 if I have Cid 11, 16 and value 33, 150

I tried

SELECT id, Bid
FROM `table`
WHERE
    Cid IN (11, 16) AND
    value IN ('33','150')
GROUP BY Bid;

But in this case I get all possible Cid’s …

It seems I’m on a wood way.

Answer

Your query is tricky because you are looking for the presence of pairs of column values in a given group. One way to go here is to aggregate by Bid, Cid, and value, first, with the restriction that each record has a matching pair. Then subquery this by Bid and check that the count be 2, indicating that both pairs were present.

SELECT Bid
FROM
(
    SELECT Bid, Cid, value
    FROM yourTable
    WHERE (Cid, value) IN ((11, 33), (16, 150))
    GROUP BY Bid, Cid, value
) t
GROUP BY Bid
HAVING COUNT(*) = 2;

Demo

Since you are using SQL Server we can slightly refactor the above query to this:

SELECT Bid
FROM
(
    SELECT Bid, Cid, value
    FROM yourTable
    WHERE (Cid = 11 AND value = 33) OR (Cid = 16 AND value = 150)
    GROUP BY Bid, Cid, value
) t
GROUP BY Bid
HAVING COUNT(*) = 2;