We help IT Professionals succeed at work.

How to SELECT a column IN a list

CMI_IT
CMI_IT asked
on
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
Comment
Watch Question

Author

Commented:
The list of reg_ids, is about 350.
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.
Top Expert 2008

Commented:
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

CERTIFIED EXPERT
Most Valuable Expert 2015

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>

Author

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>
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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.