mysql select many to many that have all condition

Question

just see this picture :

how to I select dorm_id(70)?

dorm_id is home ides , and facility is the propertray of theme , each home has some facility for example home id 70 has these facilities (12,13,14,17,18) , how to I select number 70 from dorm_id column .

its a search query that find dorm_id that has all facility

thanks in advance

Answer 1

I solve the problem , all you need is you dont see carefully the picture !

SELECT dorm_id
FROM dorm
WHERE facility_id IN (12, 13, 14)
GROUP BY doctor_id
HAVING COUNT(DISTINCT ability_id) = 3