SQL Server String Concat with Stuff

Question

I've looked online a few days now to how to use STUFF on sql server, most of the examples I see are involving only two tables and my query is going through 3 tables and I just can't get it to work here is the query without the STUFF function which gets me all the data I want :

select c.category_name,r.role_name
from categories as c 
join role_categ as rc on c.category_id=rc.category_id 
join roles as r on r.role_id=rc.role_id
where rc.c_read='1';

What I want is where you have a Category_name then Id want all the role_names in one cell in row one ex:

BCM-Télécopieur-photocopieur Admin,Administation

Here what I have with the stuff function but doesnt work jsut gives me the same table as the other query

select c.category_name,STUFF((
    select ','+r.role_name
    from roles as r
    where rc.role_id=r.role_id
    for xml path('')),1,1,'')
from role_categ as rc
join categories as c on c.category_id=rc.category_id

Any help would be appreciated.

Answer 1

Here's a version of what I came up with. @GiorgosBetsos was correct that the JOIN needs to be moved to the inner query. I'm not sure why he's still seeing duplicates, but the following query returns the data as expected:

-- Set up the data
DECLARE @roles TABLE (role_id INT, role_name VARCHAR(20))
DECLARE @role_categories TABLE (category_id INT, role_id INT)
DECLARE @categories TABLE (category_id INT, category_name VARCHAR(20))

INSERT INTO @roles (role_id, role_name) VALUES (1, 'Admin'), (2, 'Administration'), (3, 'Tech')
INSERT INTO @categories (category_id, category_name) VALUES (1, 'Consultant'), (2, 'FTP'), (3, 'Logicals')
INSERT INTO @role_categories (category_id, role_id) VALUES (1, 1), (1, 2), (1, 3), (2, 1), (2, 3), (3, 1)

-- The query
SELECT
    C.category_name,
    STUFF((
        SELECT ',' + R.role_name
        FROM
            @role_categories RC
        INNER JOIN @roles R ON R.role_id = RC.role_id
        WHERE
            RC.category_id = C.category_id AND
            RC.c_read = 1
        FOR XML PATH('')), 1, 1, '')
FROM
    @categories C