SQL repeating rows Problem

[Matrix16]

Please can anyone help.

I have a search where i search for employee by firstname or lastname or both
columns. It also searches other columns but my problem is around the firstname
and lastname.
When i have the Firstname ticked it displays the correct amount of rows.
When i have the Surname ticked it also displays the correct amout of rows.
When both are ticked it repeats the results by the amount of sites.
Now i guess its something to do with the table join and the OR part of the SQL.
Any help would be appreciated.

My query is as follows:


<cfquery datasource="mydsn" name="qry_GetEmployeeData">
SELECT

EMPLOYEE_LOOKUP.FIRST_NAME,
EMPLOYEE_LOOKUP.LAST_NAME,
EMPLOYEE_LOOKUP.MIDDLE_INITIALS,
EMPLOYEE_LOOKUP.SITE_LOCATION,
EMPLOYEE_LOOKUP.JOB_TITLE,
EMPLOYEE_LOOKUP.FAX_NUMBER,
EMPLOYEE_LOOKUP.AREA,
EMPLOYEE_LOOKUP.KO_NUMBER,
EMPLOYEE_LOOKUP.EXT_NUMBER,
EMPLOYEE_LOOKUP.DEPARTMENT,
EMPLOYEE_LOOKUP.WORK_NUMBER,
EMPLOYEE_LOOKUP.EMAIL,
EMPLOYEE_LOOKUP.EMPLOY_NUMBER,
EMPLOYEE_LOOKUP.MOBILE,
EMPLOYEE_LOOKUP.CAR_REG,
EMPLOYEE_LOOKUP.COMP_CAR,
EMPLOYEE_LOOKUP.CAR_REG2,
EMPLOYEE_LOOKUP.COST_CENTER,
EMPLOYEE_LOOKUP.CAR_MAKE,
EMPLOYEE_LOOKUP.ID,
EMPLOYEE_LOOKUP.COMMENTS,
EMPLOYEE_LOOKUP.DISPLAY_STATUS,
EMPLOYEE_LOOKUP.DISPLAY_DATE,
EMPLOYEE_LOOKUP.SAPID,
SITES.ADDRESS_LINE1,
SITES.ADDRESS_LINE2,
SITES.ADDRESS_LINE3,
SITES.ADDRESS_LINE4,
SITES.ADDRESS_LINE5,
SITES.ADDRESS_LINE6,
SITES.TELEPHONE,
SITES.FAX,
SITES.SHORT_NAME,
SITES.MAP_LINK,
SITES.COUNTRY,
SITES.LOC_PHOTO,
SITES.DIRECTIONS_LINK


FROM
UKCHAPP7.EMPLOYEE_LOOKUP, UKCHAPP7.SITES
WHERE
EMPLOYEE_LOOKUP.DISPLAY_STATUS = 'Y'
AND
EMPLOYEE_LOOKUP.DISPLAY_DATE <= '#DateFormat(Now())#'

AND
EMPLOYEE_LOOKUP.SITE_LOCATION = SITES.SITE_NAME



<!--- ########## IF THE FORENAME IS TICKED AND THE SURNAME IS TICKED SEARCH
FORENAME AND SURNAME ############### --->
<cfif isdefined('Form.SurName') AND isdefined('Form.ForeName') >
<cfif #Form.ForeName# EQ "true" AND #Form.SurName# EQ "true" AND
#SearchTerm# NEQ "" >
AND
EMPLOYEE_LOOKUP.FIRST_NAME LIKE '%#Trim(SearchTerm)#%'
OR
EMPLOYEE_LOOKUP.LAST_NAME LIKE '%#Trim(SearchTerm)#%'
</cfif>
</cfif>
<!--- ########## IF THE FORENAME IS TICKED AND THE SURNAME IS TICKED SEARCH
FORENAME AND SURNAME ############### --->



<!--- ########## IF THE FORENAME IS TICKED AND THE SURNAME IS NOT TICKED
SEARCH ONLY FOR FORENAME ############### --->
<cfif isdefined('Form.SurName') AND isdefined('Form.ForeName') >
<cfif #Form.ForeName# EQ "true" AND #Form.SurName# EQ "false" AND
#SearchTerm# NEQ "" >
AND
EMPLOYEE_LOOKUP.FIRST_NAME LIKE '%#Trim(SearchTerm)#%'
</cfif>
</cfif>
<!--- ########## IF THE FORENAME IS TICKED AND THE SURNAME IS NOT TICKED
SEARCH ONLY FOR FORENAME ############### --->



<!--- ########## IF THE FORENAME IS NOT TICKED AND THE SURNAME IS TICKED
SEARCH ONLY FOR SURNAME ############### --->
<cfif isdefined('Form.SurName') AND isdefined('Form.ForeName') >
<cfif #Form.ForeName# EQ "false" AND #Form.SurName# EQ "true" AND
#SearchTerm# NEQ "" >
AND
EMPLOYEE_LOOKUP.LAST_NAME LIKE '%#Trim(SearchTerm)#%'
</cfif>
</cfif>
<!--- ########## IF THE FORENAME IS NOT TICKED AND THE SURNAME IS TICKED
SEARCH ONLY FOR SURNAME ############### --->



<!--- ########## IF THE koNumber IS NOT TICKED SEARCH ONLY FOR KONUMBER
############### --->
<cfif isdefined('Form.KoNumber') AND isdefined('SearchTerm')>
<cfif #Form.KoNumber# EQ "true" AND #SearchTerm# NEQ "">
AND
EMPLOYEE_LOOKUP.KO_NUMBER LIKE '%#LCase(Trim(SearchTerm))#%'

</cfif>
</cfif>
<!--- ########## IF THE koNumber IS NOT TICKED SEARCH ONLY FOR KONUMBER
############### --->


<!--- ########## IF THE CAR REG IS TICKED SEARCH ONLY FOR CAR REG AND CAR REG
2 FIELDS ############### --->
<cfif isdefined('Form.CarReg')>
<cfif #Form.CarReg# EQ "true" AND #SearchTerm# NEQ "" >
AND
EMPLOYEE_LOOKUP.CAR_REG LIKE '%#UCase(Trim(SearchTerm))#%'
OR
EMPLOYEE_LOOKUP.CAR_REG2 LIKE '%#UCase(Trim(SearchTerm))#%'

</cfif>
</cfif>
<!--- ########## IF THE CAR REG IS TICKED SEARCH ONLY FOR CAR REG AND CAR REG
2 FIELDS ############### --->

ORDER BY
FIRST_NAME, LAST_NAME
ASC


</cfquery>

[draves]

In two sections you need parenthesis around the conditions with OR between them.

<!--- ########## IF THE FORENAME IS TICKED AND THE SURNAME IS TICKED SEARCH
FORENAME AND SURNAME ############### --->
<cfif isdefined('Form.SurName') AND isdefined('Form.ForeName') >
<cfif #Form.ForeName# EQ "true" AND #Form.SurName# EQ "true" AND
#SearchTerm# NEQ "" >
AND
(EMPLOYEE_LOOKUP.FIRST_NAME LIKE '%#Trim(SearchTerm)#%'
OR
EMPLOYEE_LOOKUP.LAST_NAME LIKE '%#Trim(SearchTerm)#%')
</cfif>
</cfif>
<!--- ########## IF THE FORENAME IS TICKED AND THE SURNAME IS TICKED SEARCH
FORENAME AND SURNAME ############### --->




<!--- ########## IF THE CAR REG IS TICKED SEARCH ONLY FOR CAR REG AND CAR REG
2 FIELDS ############### --->
<cfif isdefined('Form.CarReg')>
<cfif #Form.CarReg# EQ "true" AND #SearchTerm# NEQ "" >
AND
(EMPLOYEE_LOOKUP.CAR_REG LIKE '%#UCase(Trim(SearchTerm))#%'
OR
EMPLOYEE_LOOKUP.CAR_REG2 LIKE '%#UCase(Trim(SearchTerm))#%')

</cfif>
</cfif>

[Matrix16]

Hi DRAVEs,

tried the parenthesis and still get exactly the same result.

If i enter the search term of adam and have both forename and surname ticked
it returns 107 records which are repeated records. Surname ticked only returns
3 and forname ticked only returns 2.
There are 34 site entries.

Heres my modified sql.

Sorry, any other ideas how to stop this from repeating.
Matt



<cfquery datasource="UKCHAPP7" name="qry_GetEmployeeData">
SELECT

EMPLOYEE_LOOKUP.FIRST_NAME,
EMPLOYEE_LOOKUP.LAST_NAME,
EMPLOYEE_LOOKUP.MIDDLE_INITIALS,
EMPLOYEE_LOOKUP.JOB_TITLE,
EMPLOYEE_LOOKUP.FAX_NUMBER,
EMPLOYEE_LOOKUP.AREA,
EMPLOYEE_LOOKUP.KO_NUMBER,
EMPLOYEE_LOOKUP.EXT_NUMBER,
EMPLOYEE_LOOKUP.DEPARTMENT,
EMPLOYEE_LOOKUP.WORK_NUMBER,
EMPLOYEE_LOOKUP.EMAIL,
EMPLOYEE_LOOKUP.EMPLOY_NUMBER,
EMPLOYEE_LOOKUP.MOBILE,
EMPLOYEE_LOOKUP.CAR_REG,
EMPLOYEE_LOOKUP.COMP_CAR,
EMPLOYEE_LOOKUP.CAR_REG2,
EMPLOYEE_LOOKUP.COST_CENTER,
EMPLOYEE_LOOKUP.CAR_MAKE,
EMPLOYEE_LOOKUP.ID,
EMPLOYEE_LOOKUP.COMMENTS,
EMPLOYEE_LOOKUP.DISPLAY_STATUS,
EMPLOYEE_LOOKUP.DISPLAY_DATE,
EMPLOYEE_LOOKUP.SAPID,
SITES.ADDRESS_LINE1,
SITES.ADDRESS_LINE2,
SITES.ADDRESS_LINE3,
SITES.ADDRESS_LINE4,
SITES.ADDRESS_LINE5,
SITES.ADDRESS_LINE6,
SITES.TELEPHONE,
SITES.FAX,
SITES.SHORT_NAME,
SITES.SITE_NAME,
SITES.MAP_LINK,
SITES.COUNTRY,
SITES.LOC_PHOTO,
SITES.DIRECTIONS_LINK


FROM
UKCHAPP7.EMPLOYEE_LOOKUP, UKCHAPP7.SITES
WHERE
EMPLOYEE_LOOKUP.DISPLAY_STATUS = 'Y'
AND
EMPLOYEE_LOOKUP.DISPLAY_DATE <= '#DateFormat(Now())#'

AND
EMPLOYEE_LOOKUP.SITE_LOCATION = SITES.SITE_NAME



<!--- ########## IF THE FORENAME IS TICKED AND THE SURNAME IS TICKED SEARCH
FORENAME AND SURNAME ############### --->
<cfif isdefined('Form.SurName') AND isdefined('Form.ForeName') >
<cfif #Form.ForeName# EQ "true" AND #Form.SurName# EQ "true" AND
#SearchTerm# NEQ "" >
AND
(EMPLOYEE_LOOKUP.FIRST_NAME LIKE '%#Trim(SearchTerm)#%'
OR
EMPLOYEE_LOOKUP.LAST_NAME LIKE '%#Trim(SearchTerm)#%')
</cfif>
</cfif>
<!--- ########## IF THE FORENAME IS TICKED AND THE SURNAME IS TICKED SEARCH
FORENAME AND SURNAME ############### --->



<!--- ########## IF THE FORENAME IS TICKED AND THE SURNAME IS NOT TICKED
SEARCH ONLY FOR FORENAME ############### --->
<cfif isdefined('Form.SurName') AND isdefined('Form.ForeName') >
<cfif #Form.ForeName# EQ "true" AND #Form.SurName# EQ "false" AND
#SearchTerm# NEQ "" >
AND
EMPLOYEE_LOOKUP.FIRST_NAME LIKE '%#Trim(SearchTerm)#%'
</cfif>
</cfif>
<!--- ########## IF THE FORENAME IS TICKED AND THE SURNAME IS NOT TICKED
SEARCH ONLY FOR FORENAME ############### --->



<!--- ########## IF THE FORENAME IS NOT TICKED AND THE SURNAME IS TICKED
SEARCH ONLY FOR SURNAME ############### --->
<cfif isdefined('Form.SurName') AND isdefined('Form.ForeName') >
<cfif #Form.ForeName# EQ "false" AND #Form.SurName# EQ "true" AND
#SearchTerm# NEQ "" >
AND
EMPLOYEE_LOOKUP.LAST_NAME LIKE '%#Trim(SearchTerm)#%'
</cfif>
</cfif>
<!--- ########## IF THE FORENAME IS NOT TICKED AND THE SURNAME IS TICKED
SEARCH ONLY FOR SURNAME ############### --->



<!--- ########## IF THE koNumber IS NOT TICKED SEARCH ONLY FOR KONUMBER
############### --->
<cfif isdefined('Form.KoNumber') AND isdefined('SearchTerm')>
<cfif #Form.KoNumber# EQ "true" AND #SearchTerm# NEQ "">
AND
EMPLOYEE_LOOKUP.KO_NUMBER LIKE '%#LCase(Trim(SearchTerm))#%'

</cfif>
</cfif>
<!--- ########## IF THE koNumber IS NOT TICKED SEARCH ONLY FOR KONUMBER
############### --->


<!--- ########## IF THE CAR REG IS TICKED SEARCH ONLY FOR CAR REG AND CAR REG
2 FIELDS ############### --->
<cfif isdefined('Form.CarReg')>
<cfif #Form.CarReg# EQ "true" AND #SearchTerm# NEQ "" >
AND
(EMPLOYEE_LOOKUP.CAR_REG LIKE '%#UCase(Trim(SearchTerm))#%'
OR
EMPLOYEE_LOOKUP.CAR_REG2 LIKE '%#UCase(Trim(SearchTerm))#%')

</cfif>
</cfif>
<!--- ########## IF THE CAR REG IS TICKED SEARCH ONLY FOR CAR REG AND CAR REG
2 FIELDS ############### --->

ORDER BY
FIRST_NAME, LAST_NAME
ASC


</cfquery>