I am trying to select some data from a hotel database I am putting on the web. Currently I am using two temporary tables and three queries to get the data, but I suspect it can be done in one query by a better knowledge of SQL. The tables are
Hotels: ID, PostCode, ShowHotel AmenityLink: ID, HotelID, AmenityID, Value I have the postcode as $postcode and one or more amenities = 'y'; I want to find all active hotel IDs with PostCode = $postcode and having the amenityid, ignoring the amenity value as I will test for that later. SELECT Hotels.ID FROM Hotels, AmenityLink WHERE Hotels.ID = AmenityLink.HotelID AND ShowHotel = 1 AND PostCode = '$postcode' AND (AmenityLink.AmenityID = $amenityid[1] OR AmenityLink.AmenityID = $amenityid[2] ) The problem is, it is easy to test for one amenity, but I need to test that the hotel has all of the amenities. The query above returns all the hotels that have ANY of the amenities, I need the hotels having ALL of the amenties only. Thanks for any assistance. Chris Mason [EMAIL PROTECTED] Box 340, The Valley, Anguilla, British West Indies Tel: 264 497 5670 Fax: 264 497 8463 Cell: 264 235 5670 http://www.anguillaguide.com/ The Anguilla Guide Talk to me in real time: Yahoo:netconcepts_anguilla US Fax and Voicemail: (815)301-9759 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
