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);

SELECT name
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.

Try:

SELECT name
FROM hotels, facilities
WHERE hotels.id = facilities.hotel_id
AND facility_id IN (2, 7)

That's the preferred (and simpler).

Rand

Blimey that was quick, thanks. I did try that one before and again just now, this seems to return all hotels that have any facility numbered 2 OR 7. I need to to return those that only have 2 AND 7.

Thanks

Klaus

Hi,

There are couple of solutions for this. Here is one of them. I hope this will resolve your problem

SELECT #hotels.hotel_name FROM #facilities f1 INNER JOIN #facilities f2
ON f1.hotel_id = f2.hotel_id INNER JOIN #hotels ON #hotels.id= f1.hotel_id
WHERE f1.facility_id=2 AND f2.facility_id=7

nalla

Apologies for the delay in replying, I got sidetracked onto other projects. I'm back on this now and just fixed my monster sql statement.

Your solution worked. Thanks very much.