Comparing lists

[tomtomtom]

Hello

I'm trying to compare 4 lists (valuelists) and didn't really find an efficient way yet.
My lists are p.e.
<4,5,8,6,3,2,7>
<1,2,3,8>
<8,1,2,3>
<3,5,4,9,2,8>

I would like a list with the values which occur in each of the four lists, like:

<2,3,8>

For the moment I create a query with the first list and then use SQL (see sample code) to get my result, but this seems very complicated to me. Is there an easy way in CF?

Thanks for hints

[Dan_Bracuk]

You say your 4 lists are valuelists which suggests that they are the results of 4 queries. I suggest looking at those 4 queries and see if there is a way to get your answer by reducing that to 1.

[tomtomtom]

Dear Dan

Thank you for your input! I think it's a good way towards a solution. But as I am a beginner in SQL I have now the following question:

I have a table called 'ak_pr_mn' with two columns 'mn_team_ID' and 'mn_cat_ID'. At the end I would like to get a list with mn_team_ID's. Which belong to one or several mn_cat_ID's. Below my query - I don't get how to write the WHERE statement if there is more than one mn_cat_ID. And please note that my result should only show the mn_team_ID's which belong to both (or more) categories (not adding the results of the categories!).

[Dan_Bracuk]

That is a very basic question. The answer is

where cat_id in (list goes here)

To get better at sql, I have heard good things about the book, Teach Yourself SQL in 10 Minutes by Ben Forta.

[tomtomtom]

Yes I found this already out but this doesn't give me the requested result! I'm sorry I'm not familiar with the terminology but the result contains values which don't appear in ALL lists. It returs all values which are found PER list. But I need exclusively values which appear p.e. in list 1 then in list 2 and then in list 3 as described in the example in the very beginning of this topic.

Is there anyway to adjust the SQL statement - this would be great!

Thank you very much

[Kronin555 · Accepted solution]

<cfset categoryIdList = "2,3,8">
<cfquery name="test" dbtype="ODBC">
SELECT
mn_team_ID
FROM
ak_pr_mn
WHERE
mn_cat_ID IN (#categoryIdList#)
GROUP BY
mn_team_ID
HAVING
count(mn_cat_id) = #listlen(categoryIdList)#
</cfquery>

[tomtomtom]

Kronin, many thanks - this worked perfect!!!! I had to add a comma after the first SELECT argument...