Using WHERE with variables.

[mjmcevoy]

How do I format my SQL so it can select things from the database when i'm
trying to find something in a variable with multiple values.

In the below code, #FORM.building will end up returning several results. eg.
CAPEN_10,CAPEN_14,CAPEN_21.
If any of these results are in the table as "InfoSourceName" I want it to give
me the corrosponding "ComboFall".

<cfquery name="Recordset1" datasource="Fake_Matrix">
SELECT InfoSourceName, ComboFall
FROM tblRoomMatrix
WHERE InfoSourceName IN ('#FORM.building#')
</cfquery>

If I make it so that #FORM.building# only has one value, and i change the SQL
to "=" it works fine.

[JMGibson3]

IN clause is similar to =, =>, between, etc. in that if it's a text field the
values must be in quotes (with a comma separator), whereas if they're Dates or
Numbers you don't use quotes. You could build a loop to add the quotes
yourself or the ListQualify function will do it for you. Be aware that ALL
list functions ignore nulls, A,B,C,,D,,E has 4 elements not 6.

Finally, whichever method you chose, you will need the PreserveSingleQuotes
function so that your quotes don't get doubled up.

WHERE InfoSourceName IN (#PreserveSingleQuotes(myNewListWithQuotesAdded)#)

[Dan Bracuk]

Originally posted by: mjmcevoy
I'm sorry, don't know how I that is done. If you have time could you go a
little more in depth. Thank you.

Change this
WHERE InfoSourceName IN ('#FORM.building#')
to this
WHERE InfoSourceName IN (#listqualify(FORM.building)#)

Details are in the cfml reference library. If you don't have one, the
internet does.

[mjmcevoy]

Many thanks to both of you.