6 Replies Latest reply on Oct 7, 2008 10:44 AM by tomtomtom

    Comparing lists


      I'm trying to compare 4 lists (valuelists) and didn't really find an efficient way yet.
      My lists are p.e.

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


      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
        • 1. Re: Comparing lists
          Dan Bracuk Level 5
          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.
          • 2. Re: Comparing lists
            tomtomtom Level 1
            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!).
            • 3. Re: Comparing lists
              Dan Bracuk Level 5
              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.
              • 4. Re: Comparing lists
                tomtomtom Level 1
                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
                • 5. Comparing lists
                  Kronin555 Level 1
                  <cfset categoryIdList = "2,3,8">
                  <cfquery name="test" dbtype="ODBC">
                  mn_cat_ID IN (#categoryIdList#)
                  GROUP BY
                  count(mn_cat_id) = #listlen(categoryIdList)#
                  • 6. Comparing lists
                    tomtomtom Level 1
                    Kronin, many thanks - this worked perfect!!!! I had to add a comma after the first SELECT argument...