Get Unique Values 1

[MacroAlan]

I have a query that is pulling too much data. It is bringing me back everything I need plus people that are in the database with both a county in their address and with blank addresses.

I want to get one instance of each CST001_TBCS41A.PART_ID_I. I thought DISTINCT would do it but was wrong.

SELECT DISTINCT 
                 CST001_TBCS41A.PART_ID_I, 
                 CST001_TBCS41A.CLINICID_I, 
                 CST001_TBCS07A.COUNTY_C, 
                 County_Names.Name, 
                 LeedAgencies.Consortia, 
                 LeedAgencies.Agency_Name
FROM 
          	(County_Names 
        	RIGHT JOIN (CST001_TBCS41A 
         	RIGHT JOIN CST001_TBCS07A ON CST001_TBCS41A.PART_ID_I = CST001_TBCS07A.ADDR_ID_I) 
         	ON County_Names.County_C = CST001_TBCS07A.COUNTY_C) 
         	LEFT JOIN LeedAgencies ON CST001_TBCS41A.CLINICID_I = LeedAgencies.CornerNum
WHERE 
	(((CST001_TBCS41A.SERV_TYP_C)="BCS" 
           	Or (CST001_TBCS41A.SERV_TYP_C)="BCD" 
           	Or (CST001_TBCS41A.SERV_TYP_C)="CCS" 
           	Or (CST001_TBCS41A.SERV_TYP_C)="CCD") 
           	AND ((CST001_TBCS41A.ACT_PROC_D) Between #7/1/2006# And #4/30/2007#))
GROUP BY 
     	CST001_TBCS41A.PART_ID_I, 
	CST001_TBCS41A.CLINICID_I, 
	CST001_TBCS07A.COUNTY_C, 
	County_Names.Name, 
	LeedAgencies.Consortia, 
	LeedAgencies.Agency_Name, 
	CST001_TBCS41A.ACT_PROC_D
ORDER BY 
	CST001_TBCS41A.PART_ID_I, 
	CST001_TBCS41A.CLINICID_I, 
	CST001_TBCS07A.COUNTY_C
WITH OWNERACCESS OPTION;

Any ideas??


Alan

 

[PHV]

What wrong if using INNER JOIN instead of RIGHT and LEFT ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MacroAlan]

Super duper! Guess I was trying too hard to be too cute!




Alan

 

[PHV]

I forgot to warn you that using the DISTINCT predicate in an aggregate query is a nonsense.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886