Using IN in a WHERE clause

[drmaves]

I'm trying to use IN in a WHERE clause as follows: idx_res.city IN
('#session.s_city#') The value of session.s_city is 'highlands ranch, lone
tree' (value does not include the quotes) The query produces no records;
however if I use the following it works correctly: idx_res.city IN ('highlands
ranch', 'lone tree') The value for session.s_city is not the result of a query
but is contained in one field in the table. How can I produce the data in the
right format for IN to read it properly?

[PaulH]

listQualify()

[drmaves]

Thanks Paul - worked perfectly!

[drmaves]

I'm trying to make a selection based upon an element of a list being IN another
list. Here's what I have, why isn't it working?

<cfquery name="SelectListingAlert" datasource="#Application.dsn#">
SELECT *
FROM listing_alert
WHERE id = #ListingAlertID#
</cfquery>

<cfset whereclause = "0=0">

<cfif SelectListingAlert.construction NEQ "">
<cfset type_elements = ListLen(SelectListingAlert.construction)>
<cfif type_elements EQ 1>
<cfset whereclause = whereclause & " AND
'#ListFirst(SelectListingAlert.construction)#' IN construction">
</cfif>
</cfif>

<cfquery name="SelectListings" datasource="#Application.dsn#">
SELECT *
FROM idx_res
WHERE #PreserveSingleQuotes(whereclause)#
</cfquery>

The WHERE clause ends up being:

WHERE 'BR' IN construction

This; however produces the following error when the query trys to execute:

Syntax error or access violation: You have an error in your SQL syntax. Check
the manual that corresponds to your MySQL server version for the right syntax
to use near 'construction' at line 3

What am I doing wrong?

[mxstu]

AFAIK you cannot use a WHERE IN (...) clause that way. If "constuction"
contains a comma delimited list of values, unfortunately you will have to
extract the values in a CF query and use a cfloop to examine each record and
determine if the "construction" list contains the value "BR".

This is one of the downsides of storing a comma-delimited list in a single
column. Using a more normalized table structure would make this search faster
and much easier to code.

[drmaves]

Yes, "construction" could contain either one value or a comma-delimited list.
Unfortunately this is how I receive the data - I am not storing it on my end
but just reading it so I guess I'm stuck with trying to make this work.

mxstu, Would you mind providing me with an example of what your solution might
be?

[mxstu]

Do you need the records where "construction" contains at least one of the values in the list or all of the values in the list ?

[drmaves]

If any of the values in "SelectListingAlert.construction" exist in "construction" then they should be selected.

[mxstu]

Well ... that is not a good table design, but if you only have read access I
guess there is not much you can do ;-) The attached example assumes your tables
contain a unique identifier column named "ID". Note - I removed the "SELECT *
.... " statements because it decreases performance.



<!--- should use cfqueryparam for the where clause --->
<cfquery name="selectListingAlert" datasource="#yourDSN#">
SELECT Construction
FROM listing_alert
WHERE id = #ListingAlertID#
</cfquery>

<!--- get list of selected values --->
<cfset alertList = ValueList(selectListingAlert.Construction)>

<cfquery name="getListings" datasource="#yourDSN#">
SELECT ID, Construction
FROM idx_res
</cfquery>

<cfset matchedIDList = "">
<cfloop query="getListings">
<!--- does column contain at least one of selected values ? --->
<cfset wasFound = false>
<cfloop list="#alertList#" index="currItem">
<cfif listFindNoCase(getListings.construction, currItem) GT 0>
<cfset wasFound = true >
</cfif>
</cfloop>
<cfif wasFound>
<cfset matchedIDList = listAppend(matchedIDList, getListings.ID)>
</cfif>
</cfloop>

<!--- need to add check for empty list --->
<cfquery name="selectListings" dbType="query">
SELECT ID, Construction
FROM getListings
WHERE ID IN (#matchedIDList#)
</cfquery>
<cfdump var="#selectListings#">

[drmaves]

Thanks for your help. I understand what you've done but I'm not sure it will
work for what I need to accomplish.

I have a search form with about 50 fields on it that feeds my action page
where I create a WHERE clause from the data in the form. This WHERE clause is
used to query the table "idx_res".

About eight of the fields on the form contain lists that need to be compared
to fields within the "idx_res" table that also contain lists, just like the
example you just worked on.

If I take your approach I think I would have to do queries of queries to get
the correct records. Is there another way to approach this?

[mxstu]

>I have a search form with about 50 fields on it that feeds my action page
where

>I create a WHERE clause from the data in the form.

I'm not sure my understanding of your query is right. Can you give me an
example of a WHERE clause with a few fields? (It doesn't matter if the actual
SQL won't work)

[drmaves]

I'm not sure this is the best way to approach this but I found a solution.
If the approach is okay then maybe the way I did the loop could be improved.

I added some more of the code so you could see how the WHERE clause is built.

Anyway take a look and see what you think...



<cfquery name="SelectListingAlert" datasource="#Application.dsn#">
SELECT *
FROM listing_alert
WHERE id = #ListingAlertID#
</cfquery>

<cfset whereclause = "0=0">

<cfif SelectListingAlert.city NEQ "">
<cfset whereclause = whereclause & " AND city IN
(#listqualify(SelectListingAlert.city,"'",",")#)">
</cfif>

<cfset whereclause = whereclause & " AND bedrooms_total >=
#SelectListingAlert.bedrooms_from# AND bedrooms_total <=
#SelectListingAlert.bedrooms_to#">

<cfset whereclause = whereclause & " AND baths_total >=
#SelectListingAlert.baths_from# AND baths_total <=
#SelectListingAlert.baths_to#">

<cfif SelectListingAlert.construction NEQ "">
<cfset type_elements = ListLen(SelectListingAlert.construction)>
<cfif type_elements EQ 1>
<cfset whereclause = whereclause & " AND construction LIKE
'%#ListFirst(SelectListingAlert.construction)#%'">
<cfelseif type_elements GTE 2>
<cfset whereclause = whereclause & " AND (construction LIKE
'%#ListFirst(SelectListingAlert.construction)#%'">
<cfset type_element = 1>
<cfloop list="#SelectListingAlert.construction#" index="i">
<cfif type_element NEQ 1>
<cfset whereclause = whereclause & " OR construction LIKE '%#i#%'">
</cfif>
<cfset type_element = type_element+1>
</cfloop>
<cfset whereclause = whereclause & ")">
</cfif>
</cfif>

<cfquery name="SelectListings" datasource="#Application.dsn#">
SELECT *
FROM idx_res
WHERE #PreserveSingleQuotes(whereclause)#
LIMIT 100
</cfquery>

[efecto747]

Hi, this is how I would approach the above code - not much difference really
but my preference would be to build the where clause inside the query, I find
it helps with visualising the final query structure..

cheers.




<cfquery name="SelectListingAlert" datasource="#Application.dsn#">
SELECT *
FROM listing_alert
WHERE id = #ListingAlertID#
</cfquery>


<cfquery name="SelectListings" datasource="#Application.dsn#">
SELECT * <!--- avoid using * - better to specify each field --->
FROM idx_res
WHERE 0 = 0
<cfif SelectListingAlert.city NEQ "">
AND city IN (#listqualify(SelectListingAlert.city,"'",",")#)
</cfif>
AND bedrooms_total >= #SelectListingAlert.bedrooms_from#
AND bedrooms_total <= #SelectListingAlert.bedrooms_to#
AND baths_total >= #SelectListingAlert.baths_from#
AND baths_total <= #SelectListingAlert.baths_to#
<cfif SelectListingAlert.construction NEQ "">
AND (
<cfloop list="#SelectListingAlert.construction#" index="i">
construction LIKE '%#i#%'
<cfif i neq ListFirst(SelectListingAlert.construction)>
OR
</cfif>
</cfloop>
)
</cfif>
LIMIT 100
</cfquery>

[mxstu]

I don't think LIKE will produce the correct results. The reason is that LIKE
'%AB%' will find partial matches. So if you were searching for just 'AB' in a
comma delimited list, LIKE '%AB%' would also find:

construction = 'CAB,LAB,TAB'
construction = 'TABLE,ABLE'

[drmaves]

efecto747:

My users have about 50 criteria to choose from in the search form that feeds
this query so I never know what they'll use or not use so I build my
"whereclause" dynamically everytime a user performs a search. I've also found
by doing it this way it easier to manage, easier to read and easier to
troubleshoot. When testing I can display the "whereclause" variable and see
eactly what my WHERE clause looks like.

I used your technique for checking the first element in the loop, it helped
cut out a few more lines of code.

mxstu:

Good point about using LIKE; however, I am able to use LIKE in this situation
because all the values in 'construction' are unique.

Thanks to both of you for your help.

I've attached the latest version for you to review. I added some trim()
functions and removed the preceding '%' for the first element of the list.






<cfif SelectListingAlert.construction NEQ "">
<cfset type_elements = ListLen(SelectListingAlert.construction)>
<cfif type_elements EQ 1>
<cfset whereclause = whereclause & " AND construction LIKE
'#trim(ListFirst(SelectListingAlert.construction)) #%'">
<cfelseif type_elements GTE 2>
<cfset whereclause = whereclause & " AND (construction LIKE
'#trim(ListFirst(SelectListingAlert.construction)) #%'">
<cfloop list="#SelectListingAlert.construction#" index="i">
<cfif i neq ListFirst(SelectListingAlert.construction)>
<cfset whereclause = whereclause & " OR construction LIKE '%#trim(i)#%'">
</cfif>
</cfloop>
<cfset whereclause = whereclause & ")">
</cfif>
</cfif>

[drmaves]

Correction to my last code listing. I put the preceding '%' back in for the
first entry. They are necessary since I don't know where in the 'construction'
field the will occur.

Here's the corrected code:



<cfif SelectListingAlert.construction NEQ "">
<cfset type_elements = ListLen(SelectListingAlert.construction)>
<cfif type_elements EQ 1>
<cfset whereclause = whereclause & " AND construction LIKE
'%#trim(ListFirst(SelectListingAlert.construction) )#%'">
<cfelseif type_elements GTE 2>
<cfset whereclause = whereclause & " AND (construction LIKE
'%#trim(ListFirst(SelectListingAlert.construction) )#%'">
<cfloop list="#SelectListingAlert.construction#" index="i">
<cfif i neq ListFirst(SelectListingAlert.construction)>
<cfset whereclause = whereclause & " OR construction LIKE '%#trim(i)#%'">
</cfif>
</cfloop>
<cfset whereclause = whereclause & ")">
</cfif>
</cfif>