Selecting on different values of the same field
This is a bit fiddly so I hope I can explain it clearly.
Simplifying things, (using mysql btw) I have a hotels table and a facilities table a bit like this;
** hotels table **
id, hotel name
1, best western
2, comfort inn
3, holiday inn
** facilities table
id, hotel id, facility id
1, 1, 2
2, 1, 3
3, 1, 7
4, 2, 2
5, 2, 7
6, 3, 5
7, 3, 6
What I am trying to do it pull out all hotels from a list
that have certain facilities, e.g. I want all hotels that have
facilities 2 and 7.
I've tried pretty much everything but am failing
miserably. This doesn't work (I know this is obviously wrong, but it
should show what I am trying to do);
FROM hotels, facilities
WHERE hotels.id = facilities.hotel_id
and (facility_id=2 and facility_id=7)
It would be easy if it could be OR, but I need only those hotels with
ALL the facilities specficied. I've tried HAVING and that hasn't worked either.
Can anybody offer any help please?
Many many thanks.