Skip to main content
T
tomtomtom7147938
Inspiring
October 7, 2008
Answered

Comparing lists

  • October 7, 2008
  • 2 replies
  • 717 views
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
This topic has been closed for replies.
Correct answer Kronin555
<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>

2 replies

D
Dan_Bracuk
Inspiring
October 7, 2008
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.
T
tomtomtom7147938Author
Inspiring
October 7, 2008
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
K
Kronin555Correct answer
Participating Frequently
October 7, 2008
<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>
D
Dan_Bracuk
Inspiring
October 7, 2008
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.
T
tomtomtom7147938Author
Inspiring
October 7, 2008
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!).
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices