Pass-through Query Problem - SELECT DISTINCT is not working

[tyleri]

I have a problem - i have a pass-through query and it is not selecting the distinct variables that I am asking it to. below is my code - and when I execute the query it shows vw_olp_userdetail.login_id duplicates. Sometimes there are 7 or 8 of the same "login_id". can someone edit my code so this problem hopefully will not occur??? thanks

SELECT DISTINCT vw_olp_userdetail.login_id, vw_olp_userdetail.first_name AS user_frst_nm, vw_olp_userdetail.last_name AS user_last_nm, vw_olp_userdetail.mf_id, DPS_USER.mi_tx, DPS_USER.user_addr_1_tx, DPS_USER.user_addr_2_tx, DPS_USER.user_addr_3_tx, DPS_USER.user_city_cd, DPS_USER.user_state_cd, DPS_USER.user_zip_cd, DPS_USER.user_cntry_cd, DPS_USER.pers_id_tx, DPS_USER.ssn_num, DPS_USER.user_regis_dt, DPS_USER.browsr_type_cd, DPS_USER.browsr_vers_cd, DPS_USER.oper_syst_cd, DPS_USER.salutation_tx, DPS_USER.internal_user_ind, DPS_USER.suffix_tx, DPS_USER.last_logon_dt, DPS_USER.regis_class2_cd, Contact_Info.prim_email_addr_tx, Contact_Info.alt_email_addr_tx, Contact_Info.prim_phone_num, Contact_Info.alt_phone_num, Contact_Info.beeper_email_addr_tx, Contact_Info.fax_num, Bus_Fin_Info.bus_nm, Bus_Fin_Info.bus_addr_1_tx, Bus_Fin_Info.bus_addr_2_tx, Bus_Fin_Info.bus_addr_3_tx, Bus_Fin_Info.bus_city_cd, Bus_Fin_Info.bus_state_cd, Bus_Fin_Info.bus_zip_cd, Bus_Fin_Info.bus_cntry_cd, UniqueID.unique_id_tx

FROM (vw_olp_userdetail
LEFT JOIN Business_Services_Provider
ON vw_olp_userdetail.login_id = Business_Services_Provider.uid_id)
LEFT JOIN (((DPS_USER LEFT JOIN Contact_Info
ON DPS_USER.id = Contact_Info.id)
LEFT JOIN Bus_Fin_Info
ON DPS_USER.id = Bus_Fin_Info.id)
LEFT JOIN UniqueID
ON DPS_USER.unique_id_cd = UniqueID.unique_id_cd)
ON Business_Services_Provider.login_id = DPS_USER.login_id

WHERE (((Business_Services_Provider.uid_id) Is Not Null) AND ((vw_olp_userdetail.login_id) Is Not Null)) ;

 

[foxdev]

The DISTINCT clause applies to the whole record, not just your login_id. If you look carefully at the duped login_id records, I suspect you'll find that at least one other data column is different. Robert Bradley

http://www.foxdev.com/

Got extra money lying around? Visit:

http://www.agrainofhope.org

 

[fluteplr]

Read the documentation.

Select distinct checks all fields for each record against every other record. Only if ALL the fields match is it considered not distinct and thus eliminated.