How to search in mysql?

Question

how to select doctor_id which has ability_id equal 1 and 2 and also 3? ( so the answer is 70)

I know that I cant use IN statement

doctor_id| ability_id
-------- | -------------
70       |      1
71       |      2
70       |      2
70       |      3
50       |      1
20       |      2
10       |      2

Answer 1

An alternative to Gordon's query would be to aggregate by doctor, restrict to abilities in 1, 2, or 3, and then retain those doctors having three distinct abilities. This would imply that a retained doctor has all three abiliites.

SELECT doctor_id
FROM yourTable
WHERE ability_id IN (1, 2, 3)
GROUP BY doctor_id
HAVING COUNT(DISTINCT ability_id) = 3