How to SELECT a column IN a list

I have something like this:

<cfset lRegIDs = "C3AC20B4-3048-2F2D-064489365ED76B91XX,6A11F0D9-3048-2F2D-061732F9BB2EC303">

<cfquery name="qGetList" datasource="mydsn">
Select first_name,last_name,email,reg_id  from tbl
where 0 = 0
AND reg_id IN ('#lRegIDs#')
order by last_name, first_name, email ASC
</cfquery>

This doesnt work. How can I "optimally" loop thru the list and select the records
CMI_ITAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Mr_NilConnect With a Mentor Commented:
@CMI_IT,

Rather than looping through the list of RegIDs I would recommend taking a step back and look at creating a query that incorporates the query that populated lRegID with this query, so that you are only running one query and the join is done by the database instead of a very inefficient IN statement.

If you can't do that then you need to use <cfqueryparam> as suggested by agx.

An alternate solution to this is to use ListQualify() to set the qualifiers on your list.
eg. AND reg_id in (#ListQualify(lRegID,"'")#)
NOTE:  the quotes in ListQualify are double quotes surrounding a single quote.

This will work, but I would recommend looking at creating one query first and then cfqueryparam before this.
0
 
CMI_ITAuthor Commented:
The list of reg_ids, is about 350.
0
 
Jinesh KamdarCommented:
Iguess in ur case, the only option (since ur individual values are so lengthy) is to load them all into a table and then check for these values in the table rather than comapre against 350 literal vaues. You can't compare the IN operator against a single string containing these values separated by commas b'coz its still just a single string value.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
ee_rleeCommented:
try this
Select first_name,last_name,email,reg_id  from tbl
where 
#lRegIDs# LIKE reg_id + ',%'  OR
#lRegIDs# LIKE '%,' + reg_id + ',%' OR
#lRegIDs# LIKE '%,' + reg_id OR
#lRegIDs# = reg_id
order by last_name, first_name, email ASC 

Open in new window

0
 
_agx_Commented:
Use cfqueryparam with the list attribute.  Change the cfsqltype if needed

<cfquery name="qGetList" datasource="mydsn">
Select first_name,last_name,email,reg_id  from tbl
where reg_id IN
(
<cfqueryparam value="#lRegIDs#" cfsqltype="cf_sql_varchar"  LIST="true">
)
order by last_name, first_name, email ASC
</cfquery>
0
 
CMI_ITAuthor Commented:
This looks good but its does not quite the list items so it does not return a full recordset

I ended up doing something like this:

    <cfquery name="qGetList1" datasource="mydsn">
    Select first_name,last_name,email,reg_id  from #tbl#
    where 0 = 0
    AND reg_id IN (<cfloop list="#lRegIDs#" index="i">'#trim(i)#'<cfset cnt=cnt+1><cfif cnt NEQ ListLen(lRegIDs)>,</cfif></cfloop>)
    </cfquery>
0
All Courses

From novice to tech pro — start learning today.