Duplicate records

[DuLaus]

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]

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]

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]

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]

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]

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 Document</title> <meta http-equiv='Content-Type'
content='text/html; charset=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]

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 Document</title> <meta
http-equiv='Content-Type' content='text/html; charset=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>

[Poorest Leno]

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