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)) ;

 

[RickSpr]

I think you have a misunderstanding about what the DISTINCT predicate means. It eliminates rows where the entire row is a duplicate of a previous row. It doesn't work on individual fields. So you have the same 7 or 8 rows with the same login_id because they differ in the other columns you've selected.

You want only one row per login_id? Then you'll have to decide how to reduce the multiple rows: by choosing the first data that happens to occur in the other fields? By aggregating values in numeric fields? Or if you weren't expecting all these JOINs to result in multiple rows for a login_id, you'll have to figure out for yourself what was wrong with your assumptions and how to correct it. We have no way of figuring that out. Rick Sprague

 

[tyleri]

Hi - you are right I wasn't aware of the DISTINCT property. IN the database, the information is exactly the same except for one field which is meaningless to our department - and this is why there are duplicates coming up in my query. I would like to know if you know how to only show one value in a field - I don't care which entire row the "omit duplicate function" grabs, but I need it to do this. Also, unfortunately - I have to include the field that is "meaningless" to our department that varies in each of the duplicate rows...

I tried:

AND
((vw_olp_userdetail.mf_id) In (SELECT mf_id FROM vw_olp_userdetail As Tmp GROUP BY mf_id HAVING Count(*)<=1 ))

but this didn't seem to help. Are there any ways you would suggest to omit a duplicate field? Remember, i don't care which of the duplicate rows I grab....

Thanks for the help!

 

[RickSpr]

Something's not right here. You're saying you &quot;have to include the 'meaningless' field&quot;, but you don't care which row, and therefore which of the 'meaningless' field values, you get back? That's pretty hard to swallow. If you don't care what's in it, why do you &quot;have to&quot; include it? Is it just some foolish bureaucratic requirement?

I guess that if this is really true, my first suggestion would be to select a constant value 'AS' the meaningless field, thus restoring your selected data to true duplicates, and letting DISTINCT eliminate them.

Or, if you must have an actual field value for the meaningless field, try this: Use FIRST(meaningless field) in the SELECT clause, and add a GROUP BY that includes all the fields except the meaningless one. You don't need a HAVING clause. Rick Sprague

 

[tyleri]

Hey - I guess I feel the need to explain this a little more thoroughly... I am working with pass-through-queries in MS Access. I am working with a database that I have no control over too - I am constructing *this* database for viewing and counting purposes. They want to know which &quot;meaningless field&quot; I have because they want to know which one of the records they are looking at out of the group of duplicates - basically - you're right, it's meaningless bureaucratics at work! But thanks for the help - I'm going to try the suggestions.

 

[RickSpr]

Oy! You've been saying pass-through query right from the start, but I didn't pick up on it until just now. I'm not sure FIRST() is going to work for you; it depends on the SQL dialect of the database server. If it's not supported, it'll give you a syntax error.

I don't know SQL Server or Oracle SQL dialects, only Jet and IBM DB2 flavors. I hope it works, but if not you'll have to try to find another aggregate function that can be used to pick one of the 'meaningless' values for you. Rick Sprague