SELECT Construction FROM listing_alert WHERE id = #ListingAlertID# SELECT ID, Construction FROM idx_res SELECT ID, Construction FROM getListings WHERE ID IN (#matchedIDList#) [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => mxstu [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 9 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> FROM idx_res WHERE 0 = 0 AND city IN (#listqualify(SelectListingAlert.city,"'",",")#) AND bedrooms_total >= #SelectListingAlert.bedrooms_from# AND bedrooms_total <= #SelectListingAlert.bedrooms_to# AND baths_total >= #SelectListingAlert.baths_from# AND baths_total <= #SelectListingAlert.baths_to# AND ( construction LIKE '%#i#%' OR ) LIMIT 100 [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => efecto747 [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 13 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> Using IN in a WHERE clause - Coldfusion Database Access

Using IN in a WHERE clause - Coldfusion Database Access

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

  1. #1

    Default Using IN in a WHERE clause

    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?

    drmaves Guest

  2. #2

    Default Re: Using IN in a WHERE clause

    listQualify()
    PaulH Guest

  3. #3

    Default Re: Using IN in a WHERE clause

    Thanks Paul - worked perfectly!
    drmaves Guest

  4. #4

    Default Using IN in a WHERE clause

    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?


    drmaves Guest

  5. #5

    Default Re: Using IN in a WHERE clause

    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.


    mxstu Guest

  6. #6

    Default Re: Using IN in a WHERE clause

    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?

    drmaves Guest

  7. #7

    Default Re: Using IN in a WHERE clause

    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 ?
    mxstu Guest

  8. #8

    Default Re: Using IN in a WHERE clause

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

  9. #9

    Default Re: Using IN in a WHERE clause

    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#">

    mxstu Guest

  10. #10

    Default Re: Using IN in a WHERE clause

    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?

    drmaves Guest

  11. #11

    Default Re: Using IN in a WHERE clause

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

    mxstu Guest

  12. #12

    Default Re: Using IN in a WHERE clause

    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>

    drmaves Guest

  13. #13

    Default Re: Using IN in a WHERE clause

    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>

    efecto747 Guest

  14. #14

    Default Re: Using IN in a WHERE clause

    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'




    mxstu Guest

  15. #15

    Default Re: Using IN in a WHERE clause

    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 Guest

  16. #16

    Default Re: Using IN in a WHERE clause

    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>

    drmaves Guest

Similar Threads

  1. CF MX 6 WHERE Clause
    By Jeremy5431 in forum Macromedia ColdFusion
    Replies: 13
    Last Post: May 2nd, 02:26 PM
  2. Using variable in From clause
    By Glenn Stein in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 3rd, 08:45 PM
  3. help on join in from clause
    By Jen in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 3rd, 06:16 AM
  4. IN clause with 2 fields
    By Gustavo Pizzini Becker in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 2nd, 09:07 PM
  5. update and having clause??
    By JT in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 2nd, 07:52 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •