Continue to get duplicate results with SQL Query

Question

I was thinking it might be an issue with the joins? I've tried Group by to no avail... Any advice would be appreicated! I've placed the query below:

*Sorry for the lack of details- Med_Prof_Record_No is the unique value, and I know the code is messy- but this is what was here when I got here ;-) Also this is a sql 2000 box, so new syntax won't work... I've cleaned up the joins abit but don't want to stray too far from the original queries-

SELECT DISTINCT
    P.Last_name + ', ' + P.First_name AS Full_name,   
    P.Degree, 
    F.Med_Prof_Record_No, 
    F.Current_status, 
    F.Status_category, 
    F.Department_name, 
    F.Section, 
    F.SPHAffiliatedPhysiciansSurgeons AS Affiliated,
    S.Board_Name, 
    S.Specialty_Name,
    O.Office_name, 
    O.Address_1, 
    O.Address_2, 
    O.City, 
    O.State, 
    O.Zip_Code, 
    O.Phone_number_1, 
    O.Fax_number
FROM 
    Med_Prof P, Med_Prof_Facilities F, Med_Prof_Specialties S, Med_Prof_Offices O
WHERE         
    (F.Med_Prof_Record_No = P.Med_Prof_Record_No) AND 
    (F.Med_Prof_Record_No = S.Med_Prof_Record_No) AND
    (F.Med_Prof_Record_No = O.Med_Prof_Record_No) AND        
    <cfif URL.LastName is NOT "">(P.Last_name LIKE '#URL.LastName#%') AND</cfif>
    <cfif URL.Specialty is NOT "">(F.Section = '#URL.Specialty#') AND</cfif>
    <cfif URL.Group is NOT "">(O.Office_name LIKE '#URL.Group#%') AND</cfif>                
    (F.Status_category = 'active')
ORDER by Full_name

Answer 1

Here is a stab. This makes assumptions about how you want to decide which office etc. to show (in this case it is as good as arbitrary), that P.Med_Prof_Record_No is unique and only represents one person (at first I thought Last_name + First_name is unique, but that seems a very dangerous assumption), and also that you are using SQL Server 2005 or better. Finally, please use properly qualified object names and please, please, please stop using lazy implicit joins of the FROM foo, bar, blat, splunge variety.

;WITH x AS
(
  SELECT 
    P.Last_name + ', ' + P.First_name AS Full_name,   
    P.Degree, 
    F.Med_Prof_Record_No, 
    -- other columns from F,
    S.Board_Name, 
    S.Specialty_Name,
    O.Office_name,
    -- other columns from O,
    rn = ROW_NUMBER() OVER (PARTITION BY P.Med_Prof_Record_No
      ORDER BY F.Current_status, S.Board_name, O.Office_name)
  FROM 
    dbo.Med_Prof AS P
  INNER JOIN 
    dbo.Med_Prof_Facilities AS F
    ON P.Med_Prof_Record_No = F.Med_Prof_Record_No
  INNER JOIN
    dbo.Med_Prof_Specialties AS S
    ON F.Med_Prof_Record_No = S.Med_Prof_Record_No
  INNER JOIN
    dbo.Med_Prof_Offices AS O
    ON F.Med_Prof_Record_No = O.Med_Prof_Record_No
  WHERE         
    <cfif ... AND</cfif>
    -- other <cfif> clauses
    (F.Status_category = 'active')
)
SELECT * FROM x WHERE rn = 1
ORDER BY Full_name;