mysql search - return id only if it's present in all of the conditions

I have a yable for members and a table for the services that each member provides:

MemberID | ServiceID
--------------
1        | 2
1        | 3
2        | 1
2        | 3

So a member can 开发者_运维百科provide any number of services. I have a search form which lets the user check some or all of the services. I want to be able to select all of the members that provides ALL of the services that the user selected (and not just some of them). I used WHERE ... IN.., but it returns all of the members that provides at least one of the selected services. I now have a query similar to:

SELECT members.id
FROM 
members
LEFT JOIN services ON (members.id=services.memberID)
WHERE members.id IN (....)

Any help? Thank you

If I understand your question correctly, this should work:

SELECT memberID
FROM services
WHERE ServiceID IN (...)
GROUP BY memberID
HAVING COUNT(*) = x;

where (...) is a list of all the selected servicesIDs, and x is the number of items in (...)

The problem seems to be the LEFT join and the fact you are checking members.id in the where clause ("select all of the members that provides ALL of the services that the user selected", so you should be checking on servicesID):

SELECT members.id
FROM 
members
JOIN services ON (members.id=services.memberID)
WHERE servicesID IN (....)

I actually use a variation of this question when I do interviews (thanks for coming in, Mr Ant, we will let you know if we decide to move forward). If the number of services is small:

select s1.member_id from services s1, services s2, services s3
where s1.member_id = s2.member_id and s2.member_id = s3.member_id
and s1.service_id = 1 and s2.service_id = 2 and s3.service_id = 3

If the number of services is unreasonably high, but member_id,service_id is unique

select member_id from services group by member_id having count(*) = 100;

(if there are 100 services). Worse case scenario (no uniqueness guarantee):

select member_id from services group by member_id having count(distinct service_id) = 100;

Which is slow, but works.