If you know the total number of amenities, n, then you could try this:
SELECT h.ID, count(*) as total FROM AmenityLink al, Hotels h where al.HotelID=h.ID AND ShowHotel = 1 AND PostCode = '$postcode' group by h.ID having total=n Hope it helps. Cheers, Clemente Zamora GlobalReservas (http://www.clementezamora.com) "Chris Mason" <[EMAIL PROTECTED]> wrote: > > Asunto: Need a little query help > Fecha: Mon, 28 Oct 2002 08:08:41 -0400 > De: "Chris Mason" <[EMAIL PROTECTED]> > A: <[EMAIL PROTECTED]> > > 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
