> I need to be able to get the event_id's that have ONLY
> BOTH 45 and 14 other_id values (in the example data
> above, only event_id 5 would be a valid return value
> from the SQL code). If the event_id is associated with
> anything but these two other_id values, I do not want it
> returned.
> I've messed with IN, ANY and ALL list search conditions to
> no avail.
Probably you need EXISTS ...
<cfquery name="..." datasource="...">
SELECT event_id FROM events
WHERE <cfloop index="x" list="#others#">
EXISTS (SELECT event_id FROM
event_other WHERE other_id = #x#
AND event_id = events.event_id)
AND </cfloop> NOT EXISTS
(SELECT event_id FROM event_other
WHERE event_id = events.event_id
AND other_id NOT IN (#catlist#) )
</cfquery>
Not the careful placement of the AND inside the cfloop... The last AND on
the last iteration of the lop leads into the NOT which will filter out the
remaining entries...
hope this helps
Isaac
www.turnkey.to
954-776-0046
______________________________________________________________________
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
