This content has been marked as final. Show 6 replies
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.
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!).
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.
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
<cfset categoryIdList = "2,3,8">
<cfquery name="test" dbtype="ODBC">
mn_cat_ID IN (#categoryIdList#)
count(mn_cat_id) = #listlen(categoryIdList)#
Kronin, many thanks - this worked perfect!!!! I had to add a comma after the first SELECT argument...