Select the record from the query

[manoj_kothari]

<cfset myQuery = QueryNew("UserId, SelectedOptions", "Integer, Varchar")>

<cfset newRow = QueryAddRow(MyQuery, 2)>

<cfset temp = QuerySetCell(myQuery, "UserId", "1", 1)>
<cfset temp = QuerySetCell(myQuery, "SelectedOptions", "3,6,7,9", 1)>

<cfset temp = QuerySetCell(myQuery, "UserId", "2", 2)>
<cfset temp = QuerySetCell(myQuery, "SelectedOptions", "1,3,5", 2)>

<cfset temp = QuerySetCell(myQuery, "UserId", "3", 3)>
<cfset temp = QuerySetCell(myQuery, "SelectedOptions", "2,9", 3)>

Question: Find the UserId who has been selected the option number 3 or 5?

[-__cfSearching__-]

> <cfset temp = QuerySetCell(myQuery, "SelectedOptions", "1,3,5", 2)>

In the long run, the best answer is do not store delimited values: "1,3,5". You have already discovered one of the main reasons why: It is easy to insert the information but very difficult to query it. Your data should be normalized and separated into one row for each UserID and OptionID:

UserID, OptionID
2,1
2,3
2,5

Once you restructure, finding the answer to your question "Find the UserId who has been selected the option number 3 or 5?" will be easy.