Duplicates in SQL when using DISTINCT

Question

Not sure why I keep getting duplicates with this query. It should be easy, but for some reason I just cannot figure it out.

This is my query:

SELECT DISTINCT
    STUFF((SELECT 
               ';  ' + 
               CASE
                   WHEN staff.lastname IS NOT NULL 
                       THEN UPPER(REPLACE(RTRIM(staff.lastname), ' ', '') + ', ' + RTRIM(staff.firstname))
                       ELSE UPPER('Not Assigned')
               END
           FROM 
               ca_case_assign ca 
           JOIN 
               staff ON staff.username = ca.staffusername
           JOIN 
               tbl_case c on ca.appid = c.col_caseid
           WHERE 
               ca.clientusername = c.col_username 
           FOR XML PATH('')), 1, 1, '') [CaseManager]

and this is the result that I get:

LOCALSTAFF, THERESA;  LOCALSTAFF, THERESA;  O'MALLEY, ELLEN;  STAFF, STATE;  STAFF, STATE;  STAFF, STATE;  STAFF, STATE;  STAFF, STATE;  STAFF, BC;  STAFF, BC;  STAFF, BC;  STAFF, BC;  STAFF, BC;  STAFF, BC;  STAFF, BC;

Which is obviously incorrect.

Please help, thank you.

Answer 1

The inner query should have the distinct instead:

SELECT
STUFF((SELECT DISTINCT 
           ';  ' + 
           CASE
               WHEN staff.lastname IS NOT NULL 
                   THEN UPPER(REPLACE(RTRIM(staff.lastname), ' ', '') + ', ' + RTRIM(staff.firstname))
                   ELSE UPPER('Not Assigned')
           END
       FROM 
           ca_case_assign ca 
       JOIN 
           staff ON staff.username = ca.staffusername
       JOIN 
           tbl_case c on ca.appid = c.col_caseid
       WHERE 
           ca.clientusername = c.col_username 
       FOR XML PATH('')), 1, 1, '') [CaseManager]