Need Help Using the "IN" Operator in a <cfquery> 1

[LyndonOHRC]

I know this should be simple but have been banging my head on the wall for a while now.

I have a list of text values and wanted to use the IN (#var#) method in my query but I'm getting nowhere...

I'm using a small access table on a shared CF 7 site at hostmysite.com

Queries I've tried and results... I'm so confused, any help appreciated.

What I've been trying to do is:

<cfquery...../> 

<cfset NIDList=ValueList(GetOwners.NID)
<cfquery name="GetMares" datasource="HDRegistry">
 select NID, HorseName
 From Mares
 Where NID IN ('#NIDList#')
</cfquery>

However, it returns 0 records.

When I couldn't figure it out I used some actual data from the database and get these results:

<cfquery name="GetMares" datasource="HDRegistry">
 select NID, HorseName
 From Mares
 Where NID ='EH1221'
</cfquery>

returns 1 record

<cfquery name="GetMares" datasource="HDRegistry">
 select NID, HorseName
 From Mares
 Where NID ='EH1221' or NID='K81105'
</cfquery>

returns 2 records

<cfquery name="GetMares" datasource="HDRegistry">
 select NID, HorseName
 From Mares
 Where NID IN ('EH1221,K81105')
</cfquery>

returns 0 records

Thanks



Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey

 

[cfSearching]

Where NID IN ('EH1221,K81105')

The values must be quoted individually. Otherwise, the database is searching for a single record where the NID value = 'EH1221,K81105'

My preference is to cfqueryparam with the "list" attribute. Then you do not have to worry about handling quotes. However, since your results are from another query you could probably use QuotedValueList as well.

Where NID IN 
(
<cfqueryparam value="#NIDList#" list="true" cfsqltype="cf_sql_varchar">
)

That said, is there a reason you cannot just use a single query with a JOIN instead of using two queries and IN ?



----------------------------------

http://cfsearching.blogspot.com/

 

[LyndonOHRC]

That worked thank you.



Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey