query a list

[coolidge]

Hello - I'm trying produce a list of records in a table where the last name does not equal items in a list.  The code below creates a list like this:  smith,jones,walsh,fox

Is it possible to then query the employee table to find all records whose lastname does not equal smith or jones or walsh or fox?  If so, what is the best way to do that?  Thanks very much.

<cfloop list="#form.LastName#" index="i">

<cfquery name="getNames" datasource="MyDB">

select LastName from Employee where LastName= '#i#'

</cfquery>

</cfloop>

[ilssac]

The SQL IN operator, or in your case the NOT IN operator.

SELECT LastName
FROM Employee
WHERE LastName NOT IN <cfqueryparam value="#listOfNames#" list='yes' cfsqltype="cf_sql_varchar">

P.S.  Yes you could do that without the <cfqueryparam...> tag, but why would anybody do that any more.

[Dan_Bracuk]

Regarding:

P.S.  Yes you could do that without the <cfqueryparam...> tag, but why would anybody do that any more.

I had a reason last week.  It concerned a database datediff function.  The following would work.

where datediff(day, #datevariable#, current_date) whatever.

However, putting that datevariable into a cfqueryparam tag crashed the page.

[Adam Cameron.]

I had a reason last week.  It concerned a database datediff function.  The following would work.

where datediff(day, #datevariable#, current_date) whatever.

However, putting that datevariable into a cfqueryparam tag crashed the page.

[BKBK]

The code below creates a list like this:  smith,jones,walsh,fox

<cfloop list="#form.LastName#" index="i">

<cfquery name="getNames" datasource="MyDB">

select LastName from Employee where LastName= '#i#'

</cfquery>

</cfloop>

No, it doesn't. You need extra code to create such a list from the queries.

Two things seem to contradict each other here. The field form.LastName seems to represent just one last name, yet the loop  suggests a  list of last names.

Even if form.LastName represents a list, the construction of loop and query is inefficient. It is a waste of CPU resources to run a query at every step of the loop.

Is it possible to then query the employee table to find all records whose
lastname does not equal smith or jones or walsh or fox?  If so, what is
the best way to do that? 

Suppose the starting point is a list like "smith,jones,walsh,fox". Then you could proceed as follows

<cfset lastNameList = "smith,jones,walsh,fox">

<!--- put single quotes around each name --->
<cfset qualifiedNameList = listQualify(lastNameList,"'",",","CHAR")>

<cfquery name="getNames" datasource="myDB">
select lastName from Employee where lastName NOT IN (#preservesinglequotes(qualifiedNameList)#)
</cfquery>

[Adam Cameron.]

Suppose the starting point is a list like "smith,jones,walsh,fox". Then you could proceed as follows

<cfset lastNameList = "smith,jones,walsh,fox">

<!--- put single quotes around each name --->
<cfset qualifiedNameList = listQualify(lastNameList,"'",",","CHAR")>

<cfquery name="getNames" datasource="myDB">
select lastName from Employee where lastName NOT IN (#preservesinglequotes(qualifiedNameList)#)
</cfquery>

You could save a bit of horsing around there by dispensing with the qualifying the list within the variable and then having to escape the quotes in the query: just use <cfqueryparam>.

Even it - for some reason - you needed to use a qualified list in the SQL string, then do the listQualify() bit within the <cfquery> tags, which means you don't need the preserveSingleQuotes() call.

But using a <cfqueryparam> is the best option here.

--

Adam

[BKBK]

a bit of horsing around

Not necessarily. Put a single quote in one of the names and you'll see why not.

What I give is a general sweep. Having said that, I can see your suggestion tightens things up. .