How to get records that meet all criteria in a list

[scorniglia]

Hi All,
I'm having a rather perplexing problem and I hope someone can point me in the direction of a solution. I have a table that stores GroupID's and Activity Numbers. Each GroupID can have multiple Activity Numbers such as:

GroupID ActivityNumber
1 2
1 6
1 7
2 6
2 8
3 2
3 6
3 9

The user submits a list of Activities from checkboxes on a form. I want to be able to find only the GroupID's that contain all of the Activities in the list rather than any of them. So if the list passed from the checkbox form is (2,6,7) I want the query to return only GroupID 1. The IN query returns GroupID 1,2 and 3. If the list is (2,8,9) then there should not be any GroupID's returned. I have been having a lot of trouble with this and just can't get it to work correctly. If anybody has any suggestions or thoughts on how would be best to accomplish this I would greatly appreciate it.

Regards,
Roy F.

 

[JHoarty]

Well, there's usually more than one way to skin a cat, and this doesn't feel all that elegant, but it worked:

I made a table called "test" and gave it two fields, "GroupID" and "ActivityNubmer"
and the following values for each, respectively:

1 2
1 4
1 8
2 3
2 4
2 9
3 6
3 9

and ran this:

<cfset mylist = &quot;2,4,8&quot;>
<cfset mylistlen = LISTLEN(mylist,&quot;,&quot>
<cfset counter = 1>

<cfquery datasource = &quot;quickestore&quot; name=&quot;test&quot;>
SELECT DISTINCT GroupID FROM testtable
WHERE 1 = 1
<cfloop condition = &quot;counter LTE mylistlen&quot;>
AND EXISTS (
SELECT GroupID FROM testtable
WHERE ActivityNumber = #GetToken(mylist, counter, &quot;,&quot#
<cfset counter = counter + 1>
)
</cfloop>

</cfquery>


<cfoutput>
#test.GroupID#
</cfoutput>

and got the result &quot;1&quot; as desired.
And I'll admit this was kind of tricky! John Hoarty
jhoarty@quickestore.com

http://www.quickestore.com

 

[scorniglia]

Hi John,
Thanks a lot for the help. I actually came across an answer to this problem that I don't really understand but it works very well. It was given to me by Dan Dohan on one of the ColdFusion newsgroups and solves this problem. I thought I'd share it here for anyone else interested in using a list in a query like this:

<<<<To do this, you'll need to run a self-join query, where you join multiple instances of the same table. If you named your checkbox form elements &quot;activity&quot;, your query would look like this:

<CFQUERY datasource=&quot;yourSource&quot; name=&quot;runQuery&quot;>
SELECT DISTINCT theTable.GroupID from theTable
<cfloop index=&quot;x&quot; list=&quot;#activity#&quot;>
,theTable as c#x#
</cfloop>
WHERE 1=1
<cfloop index=&quot;x&quot; list=&quot;#activity#&quot;>
AND c#x#.ActivityNumber=#x#
AND c#x#.GroupID = theTable.GroupID
</cfloop>
</CFQUERY>

Then you'd get only GroupID's that contained ALL of the activities submitted.>>>>

Thanks again for taking the time to respond and I'll check out your solution also.

Regards,
Roy F.