CODEWITHSUNDEEP
mysqlmany-to-many
kiamoz
kiamoz

Reputation: 714

mysql select many to many that have all condition

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

Upvotes: 0

Views: 142

Answers (2)

kiamoz
kiamoz

Reputation: 714

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

Upvotes: 0

nacho
nacho

Reputation: 5397

You can do it this way if you have your facility_id in a table called table_facility:

select dorm_id 
from table_dorm
group by dorm_id
having count(distinct facility_id)=(select count(distinct facility_id)
                                    from table_facility)

If you have them in the same table you can do it with this query:

select dorm_id 
from table_dorm
group by dorm_id
having count(distinct facility_id)=(select count(distinct facility_id)
                                    from table_dorm)

Upvotes: 1

Related Questions