DISTINCT Keyword Not Working In SQL

[scripter73]

Hi,

I'm still receiving duplicate records even though I use the Distinct Keyword in SQL. Is my syntax correct?


<cfquery name=&quot;newdata&quot; datasource=&quot;#session.dsn#&quot;>
SELECT DISTINCT
PLWEBNEW.wnew_policy_alpha,
PLWEBNEW.wnew_policy_number,
PLWEBNEW.wnew_agent_code,
PLWEBNEW.wnew_issued_date,
PLWEBNEW.wnew_pol_code,
PLWEBNEW.wnew_master_agent,
PLWEBPOL.wpol_renewal_seq_number,
PLWEBPOL.wpol_endorsement_no,
PLWEBPOL.wpol_ins_first_name,
PLWEBPOL.wpol_ins_last_name,
PLWEBPOL.wpol_ins_mi,
PLWEBPOL.wpol_binder_number,
PLWEBPOL.wpol_eff_date,
PLWEBPOL.wpol_quoted_tot
FROM PLWEBNEW,PLWEBPOL
WHERE (wnew_master_agent = '#session.agentcode#' AND wnew_pol_code = 'N'
AND PLWEBNEW.wnew_policy_alpha = PLWEBPOL.wpol_policy_alpha AND PLWEBNEW.wnew_policy_number = PLWEBPOL.wpol_policy_number)
ORDER BY wnew_issued_date
</cfquery>



Any help's appreciated.

Thanks,
scripter73
Change Your Thinking, Change Your Life.

 

[mkistler]

Make sure you are viewing all of the selected columns and check your results again carefully. If DISTINCT is not weeding out duplicates, then at least one column must have differing values between the otherwise identical records. Sometimes the difference is a space at the end of the data. You may be missing a necessary JOIN.

 

[scripter73]

hi mkistler,

You were right. After printing out each field, I noticed there was a tiny field that made the records different. I've since added an extra where clause and now I don't have any duplicates.

Thanks for your help,
scripter73
Change Your Thinking, Change Your Life.