Assuming you have not listed any amenities more than once for a property:
select propertyid, count(*)
from property_amenities
where amenityid in (#amenity_list#)
having count(*) = #the_number_of_items_in_amenity_list#
Steve
-------------------------------------
Steven Monaghan
Oracle DBA / Cold Fusion Developer
MSC Industrial Direct Co., Inc.
Melville, NY
[EMAIL PROTECTED]
http://www.mscdirect.com
-------------------------------------
-----Original Message-----
From: Jim McAtee [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, September 25, 2001 2:56 PM
To: CF-Talk
Subject: SQL Query Quandary
I've got three tables describing lodging properties and their amenities, as
shown below. If a lodging property has more than one amenity (for example,
hot tub, min-ibar, fireplace, etc) then there are multiple records for that
property in the property_amenities table. Pretty basic stuff.
properties
-----------------------------
propertyid autonumber
propertyname text
amenities
-----------------------------
amenityid autonumber
amenitydescription text
property_amenities
-----------------------------
property_amenityid autonumber
propertyid number
amenityid number
I need to form a search for all properties that meet the criteria of having
all of the amenities that a user asks for. Not an OR, but, logically an
AND - all properties that have ALL of the amenities ask for. The user
interface is a form with check boxes, so that when the form is submitted I
have a comma delimited list of amenityid's to work with. Given the above
table structure, I have no idea how to form this query.
Thanks for any assistance,
Jim
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
