SELECT DISTINCT city, stateprovince, country FROM HOTEL_Active WHERE city = '#form.city#''#URL.city#' ORDER BY stateprovince #city#, #stateprovince#, #country#
SELECT city, stateprovince, country FROM Hotel_Active WHERE city = '#form.city#''#URL.city#' ORDER BY stateprovince #city#, #stateprovince#, #country#
[allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => DuLaus [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] => 1 [islastshown] => [isfirstshown] => 1 [attachments] => [allattachments] => ) --> SELECT DISTINCT city, stateprovince, country FROM HOTEL_Active WHERE city = '#form.city#''#URL.city#' ORDER BY stateprovince #city#, #stateprovince#, #country#
SELECT city, stateprovince, country FROM Hotel_Active WHERE city = '#form.city#''#URL.city#' ORDER BY stateprovince #city#, #stateprovince#, #country#
[allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => DuLaus [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] => 2 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> SELECT DISTINCT city, stateprovince, country FROM HOTEL_Active WHERE city = '#form.city#''#URL.city#' AND stateprovince='#URL.stateprovince#' ORDER BY stateprovince City #city#, #stateprovince#, #country#
SELECT city, stateprovince, country FROM Hotel_Active WHERE city = '#URL.city#' AND stateprovince = '#URL.stateprovince#' #city#, #stateprovince#, #country#
[allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => DuLaus [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] => 3 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> SELECT DISTINCT city, stateprovince, country FROM HOTEL_Active WHERE city = '#form.city#''#URL.city#' AND stateprovince='#URL.stateprovince#' ORDER BY stateprovince SELECT city, stateprovince, country FROM Hotel_Active WHERE city = '#form.city#' '#URL.city#' AND stateprovince='#URL.stateprovince#' City #qGetDuplicate.city#, #qGetDuplicate.stateprovince#, #qGetDuplicate.country#
#qGetCity.city#, #qGetCity.stateprovince#, #qGetCity.country#
[allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => DuLaus [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] => 4 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> SELECT DISTINCT city, stateprovince, country FROM HOTEL_Active WHERE city = '#form.city#''#URL.city#' AND stateprovince='#URL.stateprovince#' ORDER BY stateprovince SELECT HotelID, city, stateprovince, country FROM Hotel_Active WHERE city = '#form.city#' '#URL.city#' AND stateprovince='#URL.stateprovince#' City #qGetDuplicate.city#, #qGetDuplicate.stateprovince#, #qGetDuplicate.country#
#qGetCity.hotelID#, #qGetCity.city#, #qGetCity.stateprovince#, #qGetCity.country#
[allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => DuLaus [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] => 5 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> SELECT DISTINCT city, stateprovince, country FROM HOTEL_Active WHERE city = '#form.city#''#URL.city#' AND stateprovince='#URL.stateprovince#' SELECT HotelID, city, stateprovince, country FROM Hotel_Active WHERE city = '#form.city#' '#URL.city#' AND stateprovince='#URL.stateprovince#' Untitled Document
#City#, #stateprovince#, #country#
#qGetCity.hotelID#, #qGetCity.city#, #qGetCity.stateprovince#, #qGetCity.country#
[allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => DuLaus [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] => 6 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) -->
#City#, #stateprovince#, #country#
#qGetCity.hotelID#, #qGetCity.city#, #qGetCity.stateprovince#, #qGetCity.country#
[allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => DuLaus [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] => 7 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> Duplicate records - Coldfusion - Advanced Techniques

Duplicate records - Coldfusion - Advanced Techniques

Hi I don't know if this is the best way to query for duplicate records but this works. If someone has a better idea to do this please feel free to mention it. This is the action page (results.cfm)being passed a #city# form field from a search template. The city Washington has eight different locations in the US so I have put another query for this using distinct and recordcount. WASHINGTON, , GB WASHINGTON, CT, US WASHINGTON, DC, US WASHINGTON, IL, US WASHINGTON, IN, US WASHINGTON, MO, US WASHINGTON, NC, US WASHINGTON, PA, US If there is duplicates then show ...

  1. #1

    Default Duplicate records

    Hi I don't know if this is the best way to query for duplicate records but
    this works. If someone has a better idea to do this please feel free to mention
    it. This is the action page (results.cfm)being passed a #city# form field from
    a search template. The city Washington has eight different locations in the US
    so I have put another query for this using distinct and recordcount.
    WASHINGTON, , GB WASHINGTON, CT, US WASHINGTON, DC, US WASHINGTON, IL, US
    WASHINGTON, IN, US WASHINGTON, MO, US WASHINGTON, NC, US WASHINGTON, PA, US
    If there is duplicates then show them and if not proceed to the next query.
    What I would like to do is if there are duplicates use this same template
    (results.cfm) to process the results. In other words create a URL from the
    'duplicates' query and then pass it to the 'city' query on the same template.
    I am trying to avoid using more than one template to process the results of a
    search. I do not know if this is even practical... Thanks <!--- check to
    see is more than one city eg: Washington is in database---> <cfquery name
    ='duplicate' datasource='va_db'> SELECT DISTINCT city, stateprovince, country
    FROM HOTEL_Active WHERE city = <cfif
    isDefined('form.city')>'#form.city#'<cfelse>'#URL. city#'</cfif> ORDER BY
    stateprovince </cfquery> <!--- if yes then show city and state and country
    once ---> <cfif duplicate.recordcount gt 1> <cfoutput query='duplicate'>
    #city#, #stateprovince#, #country#<br> </cfoutput> <!--- if no duplicate
    cities process this query and on this page by passing a URL variable from
    above duplicates query ---> <cfelse> <cfquery name='city' datasource='va_db'>
    SELECT city, stateprovince, country FROM Hotel_Active WHERE city = <cfif
    isDefined('form.city')>'#form.city#'<cfelse>'#URL. city#'</cfif> ORDER BY
    stateprovince </cfquery> <!--- output information ---> <cfoutput
    query='city'> #city#, #stateprovince#, #country#<br> </cfoutput> </cfif>

    DuLaus Guest

  2. #2

    Default Re: Duplicate records

    Hi again... I am trying to test if there are duplicates in the database
    (cities) and if yes then show these cities and (pass those results again to the
    next query using a URL variable?) and if no duplicates process second query.
    Thanks <cfparam name='form.city' default=''> <!--- check to see is more than
    one city eg: Washington is in database---> <cfquery name ='duplicate'
    datasource='va_db'> SELECT DISTINCT city, stateprovince, country FROM
    HOTEL_Active WHERE city = <cfif
    isDefined('form.city')>'#form.city#'<cfelse>'#URL. city#'</cfif> ORDER BY
    stateprovince </cfquery> <cfif duplicate.recordcount gt 1> <!--- if yes then
    show city and state and country once ---> <cfoutput query='duplicate'> <a
    href='searchcity.cfm?city=#duplicate.city#&amp;sta teprovince=#duplicate.statepro
    vince#'>#city#, #stateprovince#, #country#</a><br> </cfoutput> <cfelse> <!---
    if no duplicate cities process this query ---> <cfquery name='city'
    datasource='va_db'> SELECT city, stateprovince, country FROM Hotel_Active
    WHERE city = <cfif
    isDefined('form.city')>'#form.city#'<cfelse>'#URL. city#'</cfif> ORDER BY
    stateprovince </cfquery> <!--- output information ---> <cfoutput
    query='city'> #city#, #stateprovince#, #country#<br> </cfoutput> </cfif>

    DuLaus Guest

  3. #3

    Default Re: Duplicate records

    Almost there...i think. The query 'city' is giving me the problem below.
    When the form is submitted and there are duplicate cities in the db then all
    works fine, including the links pointing back to the query . If there are no
    duplicate cities then there is no output period ('cities' query). I realize I
    have no 'form.city' in the where clause of the city query, but any attempts I
    have had just throw everything off. The submit form also has some hard coded
    url links for cities which are processed on this page, which work as well,
    until I start tinkering with the lower 'cities' query. <!--- check to see is
    more than one city eg: Washington is in database---> <cfquery name
    ='duplicate' datasource='va_db'> SELECT DISTINCT city, stateprovince, country
    FROM HOTEL_Active WHERE city = <cfif
    isDefined('form.city')>'#form.city#'<cfelse>'#URL. city#' AND
    stateprovince='#URL.stateprovince#'</cfif> ORDER BY stateprovince </cfquery>
    <!--- If more than one record hyperlink back to this template processing the
    above query 'duplicate' ---> <cfif duplicate.recordcount gt 1> <cfoutput
    query='duplicate'> <a
    href='searchcity.cfm?city=#city#&amp;stateprovince =#stateprovince#&amp;country=#
    country#'>City</a> #city#, #stateprovince#, #country#</a><br> </cfoutput>
    <!--- end the duplicate stuff ---> <!-- process the query below from
    form.variables and url.variables. ---> <cfelse> <!--- This below will
    give me zero results ---> <!--- any attempts to change the where clause
    throws everything off including using '#form.city#' ---> <cfif NOT
    isDefined('form.city')> <cfquery name='city' datasource='va_db'> SELECT city,
    stateprovince, country FROM Hotel_Active WHERE city = '#URL.city#' AND
    stateprovince = '#URL.stateprovince#' </cfquery> <cfoutput query='city'>
    #city#, #stateprovince#, #country#<br> </cfoutput> </cfif> </cfif>

    DuLaus Guest

  4. #4

    Default Re: Duplicate records

    Almost there... If the #qGetDuplicate.stateprovince# is an empty string then
    no results will be shown, as coutries outside North America do not have a state
    or province. Can anyone suggest...thanks. <!--- check to see is more than
    one city eg: Washington is in database---> <cfquery name ='qGetDuplicate'
    datasource='va_db'> SELECT DISTINCT city, stateprovince, country FROM
    HOTEL_Active WHERE city = <cfif
    isDefined('form.city')>'#form.city#'<cfelse>'#URL. city#' AND
    stateprovince='#URL.stateprovince#'</cfif> ORDER BY stateprovince </cfquery>
    <cfquery name='qGetCity' datasource='va_db'> SELECT city, stateprovince,
    country FROM Hotel_Active WHERE city = <cfif
    isDefined('form.city')>'#form.city#' <cfelse>'#URL.city#' AND
    stateprovince='#URL.stateprovince#'</cfif> </cfquery> <!--- If more than one
    record hyperlink back to this template processing of above query 'duplicate'
    ---> <cfif qGetDuplicate.recordcount gt 1> <cfoutput query='qGetDuplicate'>
    <a
    href='searchcity.cfm?city=#city#&amp;stateprovince =#stateprovince#&amp;country=#
    country#'>City</a> #qGetDuplicate.city#, #qGetDuplicate.stateprovince#,
    #qGetDuplicate.country#</a><br> </cfoutput> <cfelse> <cfoutput
    query='qGetCity'> #qGetCity.city#, #qGetCity.stateprovince#,
    #qGetCity.country#<br> </cfoutput> </cfif> <cfdump var ='#qGetCity#'>

    DuLaus Guest

  5. #5

    Default Re: Duplicate records

    What this query does is check for duplicate cities (qGetDuplicate) and if there
    are duplicates then get those records with state and country, create a
    hyperlink with variables to re-submit via URL. If there no duplicate cities go
    to the next (qGetCity) query and move on. problem... If the
    #qGetDuplicate.stateprovince# is an empty string then no results will be shown,
    as countries outside North America do not have a state or province. I have
    also run into a problem with the 'qGetDuplicate' query attempting to get an
    ID and append it to the URL as I need this ID. I think my DISTINCT in the SQL
    query may be the problem. This is not a problem with the second (qGetCity)
    query. Thanks. <!--- check to see is more than one city eg: Washington is in
    database---> <cfquery name ='qGetDuplicate' datasource='va_db'> SELECT
    DISTINCT city, stateprovince, country FROM HOTEL_Active WHERE city = <cfif
    isDefined('form.city')>'#form.city#'<cfelse>'#URL. city#' AND
    stateprovince='#URL.stateprovince#'</cfif> ORDER BY stateprovince </cfquery>
    <cfquery name='qGetCity' datasource='va_db'> SELECT HotelID, city,
    stateprovince, country FROM Hotel_Active WHERE city = <cfif
    isDefined('form.city')>'#form.city#' <cfelse>'#URL.city#' AND
    stateprovince='#URL.stateprovince#'</cfif> </cfquery> <!--- If more than one
    record hyperlink back to this template processing of above query 'duplicate'
    ---> <cfif qGetDuplicate.recordcount gt 1> <cfoutput query='qGetDuplicate'>
    <!a
    href='backup/searchcity.cfm?city=#city#&amp;stateprovince=#stat eprovince#&amp;co
    untry=#country#'>City<!/a> #qGetDuplicate.city#, #qGetDuplicate.stateprovince#,
    #qGetDuplicate.country#</a><br> </cfoutput> <cfelse> <cfoutput
    query='qGetCity'> #qGetCity.hotelID#, #qGetCity.city#,
    #qGetCity.stateprovince#, #qGetCity.country#<br> </cfoutput> </cfif> <cfdump
    var ='#qGetCity#'>

    DuLaus Guest

  6. #6

    Default Re: Duplicate records

    This works with the exception of allowing a empty string or null value in the
    #stateprovince# variable in both query's below. Thanks... <!--- check to
    see is more than one city eg: Washington is in database---> <cfquery name
    ='qGetDuplicate' datasource='va_db'> SELECT DISTINCT city, stateprovince,
    country FROM HOTEL_Active WHERE city = <cfif
    isDefined('form.city')>'#form.city#'<cfelse>'#URL. city#' AND
    stateprovince='#URL.stateprovince#'</cfif> </cfquery> <cfquery name='qGetCity'
    datasource='va_db'> SELECT HotelID, city, stateprovince, country FROM
    Hotel_Active WHERE city = <cfif isDefined('form.city')>'#form.city#'
    <cfelse>'#URL.city#' AND stateprovince='#URL.stateprovince#'</cfif> </cfquery>
    <html> <head> <title>Untitled Doent</title> <meta http-equiv='Content-Type'
    content='text/html; cht=windows-1252'> </head> <!--- If more than one
    record hyperlink back to this template processing of above query 'duplicate'
    ---> <cfif qGetDuplicate.recordcount gt 1> <cfoutput query='qGetDuplicate'>
    <body> <table width='770' border='1' cellspacing='0' cellpadding='0'> <tr>
    <td><!!!a
    href='hotel.cfm?city=#city#&amp;stateprovince=#sta teprovince#&amp;country=#count
    ry#'> #City#, #stateprovince#, #country#<!!!/a></td> </tr> </table>
    </cfoutput> <cfelse> <cfoutput query='qGetCity'> <table width='770'
    border='1' cellspacing='0' cellpadding='0'> <tr> <td> #qGetCity.hotelID#,
    #qGetCity.city#, #qGetCity.stateprovince#, #qGetCity.country#<br> </td> </tr>
    </table> </cfoutput> </cfif> </body> </html>

    DuLaus Guest

  7. #7

    Default Re: Duplicate records

    The db is Access 2000 which I don't think is my problem... When a URL is
    clicked and there is NO 'stateprovince' then there will be no results. All else
    works fine if there is a 'stateprovince'. Half of the database records have a
    NULL or '' in 'stateprovince' column. I have tried using IS NULL and = ''
    in the query but this renders the form.city variable useless. Any ideas please
    ..... <CFPARAM name='URL.stateprovince' type='string' default=''> <CFQUERY
    name='qGetDuplicate' datasource='va_db'> SELECT DISTINCT city, stateprovince,
    country FROM hotel_active WHERE city = <CFIF
    IsDefined('FORM.city')>'#FORM.city#'<CFELSE>'#URL. city#' AND stateprovince =
    '#URL.stateprovince#' ;</CFIF> </CFQUERY> <CFQUERY name='qGetCity'
    datasource='va_db'> SELECT hotelID, city, stateprovince, country FROM
    hotel_active WHERE city = <CFIF IsDefined('FORM.city')>'#FORM.city#'
    <CFELSE>'#URL.city#' AND stateprovince = '#URL.stateprovince#'; </CFIF>
    </CFQUERY> <html> <head> <title>Untitled Doent</title> <meta
    http-equiv='Content-Type' content='text/html; cht=windows-1252'> </head>
    <!--- If more than one record hyperlink back to this template processing of
    above query 'duplicate' ---> <cfif qGetDuplicate.recordcount gt 1> <cfoutput
    query='qGetDuplicate'> <body> <table width='770' border='1' cellspacing='0'
    cellpadding='0'> <tr> <td> <!a
    href='hotel.cfm?city=#city#&amp;stateprovince=#sta teprovince#&amp;country=#count
    ry#'> #City#, #stateprovince#, #country#<!/a> </td> </tr> </table>
    </cfoutput> <cfelse> <cfoutput query='qGetCity'> <table width='770'
    border='1' cellspacing='0' cellpadding='0'> <tr> <td> #qGetCity.hotelID#,
    #qGetCity.city#, #qGetCity.stateprovince#, #qGetCity.country#<br> </td> </tr>
    </table> </cfoutput> </body></cfif> </html>

    DuLaus Guest

  8. #8

    Default Duplicate records

    Sorry for cross posting. I was told to 'move' my post to this forum but I can
    only duplicate.

    My SQL code below is returning duplicate records and I'm not entirely sure why
    it's happening. I have a strong feeling that its due to having so many INNER
    JOINS but I'm not sure how to go about making my SQL more simple.

    Any advice is much apreciated.
    Thanks,
    Ben

    SELECT
    DNUCLEUS.REFERENCE
    , DNUCLEUS.STATUS
    , DNUCLEUS.DEPARTMENT
    , DNUCLEUS.CODE
    , TWIP.INVOICE_AMOUNT
    , DPERSONAL.RESIDENCE
    , DPERSONAL.BIRTH
    , DRELATION_1.RELATION_TYPE
    , DNUCLEUS_2.REFERENCE
    , DRELATION.RELATION_TYPE
    , TWIP.JOB_CODE
    , TINVHEAD.INV_DATE
    , DNUCLEUS.PARTY_TYPE
    FROM
    Parties.dbo.DNUCLEUS DNUCLEUS
    INNER JOIN Parties.dbo.DRELATION DRELATION_1 ON
    DNUCLEUS.CODE=DRELATION_1.PARENT_CODE
    INNER JOIN Parties.dbo.DRELATION DRELATION ON
    DNUCLEUS.CODE=DRELATION.PARENT_CODE
    INNER JOIN Parties.dbo.TINVHEAD TINVHEAD ON DNUCLEUS.CODE=TINVHEAD.PARTY_CODE
    INNER JOIN Parties.dbo.DNUCLEUS DNUCLEUS_1 ON
    DRELATION_1.CHILD_CODE=DNUCLEUS_1.CODE
    INNER JOIN Parties.dbo.DPERSONAL DPERSONAL ON DNUCLEUS_1.CODE=DPERSONAL.CODE
    INNER JOIN Parties.dbo.DNUCLEUS DNUCLEUS_2 ON
    DRELATION.CHILD_CODE=DNUCLEUS_2.CODE
    INNER JOIN Parties.dbo.TWIP TWIP ON (TINVHEAD.PARTY_CODE=TWIP.PARTY_CODE) AND
    (TINVHEAD.INV_NO=TWIP.INV_NUM)
    WHERE
    (DNUCLEUS.PARTY_TYPE='CO' OR DNUCLEUS.PARTY_TYPE='TR')
    AND NOT (DNUCLEUS.DEPARTMENT='ADM' OR DNUCLEUS.DEPARTMENT='IAD' OR
    DNUCLEUS.DEPARTMENT='SHE')
    AND (DNUCLEUS.STATUS='L' OR DNUCLEUS.STATUS='O')
    AND DRELATION.RELATION_TYPE='URM'
    AND DRELATION_1.RELATION_TYPE='UCL'
    AND NOT (TWIP.JOB_CODE='CHQ' OR TWIP.JOB_CODE='OTHER' OR
    TWIP.JOB_CODE='PHOTO' OR TWIP.JOB_CODE='POSTAG' OR TWIP.JOB_CODE='RECOVE' OR
    TWIP.JOB_CODE='TELEPH')
    AND (TINVHEAD.INV_DATE>={ts '2004-10-01 00:00:00'}
    AND TINVHEAD.INV_DATE<{ts '2005-09-30 11:59:59'})
    ORDER BY
    DNUCLEUS.CODE

    Poorest Leno Guest

Similar Threads

  1. Return Records and not duplicate them :: Again
    By The Ox in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 16th, 05:57 PM
  2. select all the duplicate records
    By atse in forum ASP
    Replies: 9
    Last Post: October 19th, 06:59 PM
  3. Deleting duplicate records
    By Dinesh.T.K in forum FileMaker
    Replies: 3
    Last Post: August 13th, 12:00 AM
  4. Need help serializing duplicate records
    By Scott in forum FileMaker
    Replies: 2
    Last Post: July 30th, 06:24 PM
  5. Duplicate records?
    By Bridget Eley in forum FileMaker
    Replies: 2
    Last Post: July 28th, 12:06 AM

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
  •