SQL Stuff Function Showing All Results

Question

I am wanting to see a list of codes with their relevant chapters in my database. A code can have more than one chapter assigned, therefore meaning more than one row in the database.

This is how the database looks:

Code     |     Chapter
000      |     1.1.0
000      |     3.0.0
123      |     1.1.0
123      |     1.2.0
456      |     1.7.0
789      |     1.2.0
789      |     2.1.4
789      |     5.1.7

I would like to do a STUFF function to display the Code and the Chapter/s on one line, but for every Code in the system. This is how I would like it to look:

Code     |     Chapter
000      |     1.1.0, 3.0.0
123      |     1.1.0, 1.2.0
456      |     1.7.0
789      |     1.2.0, 2.1.4, 5.1.7

But I am receiving the following:

Code     |     Chapter
000      |     1.1.0, 1.1.0, 1.2.0, 1.2.0, 1.7.0, 2.1.4, 3.0.0, 5.1.7
123      |     1.1.0, 1.1.0, 1.2.0, 1.2.0, 1.7.0, 2.1.4, 3.0.0, 5.1.7
456      |     1.1.0, 1.1.0, 1.2.0, 1.2.0, 1.7.0, 2.1.4, 3.0.0, 5.1.7
789      |     1.1.0, 1.1.0, 1.2.0, 1.2.0, 1.7.0, 2.1.4, 3.0.0, 5.1.7

The SQL I am using is:

select distinct 
       C.Code,  
       STUFF((SELECT ', ' + CHAP.[Key]
             FROM Chapters CHAP
             ORDER BY CHAP.[Key]
             FOR XML PATH('')), 1, 1, '') AS [Chapter]
From Codes C
left outer join CodeChapterLinks CCL
ON C.Id = CCL.CodeId
left outer join Chapters CHAP
ON CCL.ChapterId = CHAP.Id
ORDER BY C.Code asc

Any assistance is much appreciated :)

Answer 1

Your code shows 3 tables, could you describe their structure?

Also, your code is using for xml on Chapter table, which, I assume, is the reference of the codes, not the codes that are actually used in chapter. So, may be better to use xml for path on CodeChapterLinks table.

Like:

select  distinct
       Code,  
       STUFF((SELECT ', ' + c.chapter
             FROM CodeChapterLinks  c
             where c.code = t.code
             ORDER BY c.chapter
             FOR XML PATH('')), 1, 1, '') AS [Chapter]
From CodeChapterLinks  t

And by the way, stuff is not the function that concatenates the results, for xml does.

Answer 2

Using irimias' reply, I managed to achieve what I was looking for with the following:

select distinct
          C.Code,  
          STUFF((SELECT ', ' + CHAP.[Key]
                 FROM CodeChapterLinks CCL
                 Join Chapters CHAP on CCL.ChapterId = CHAP.Id
                 where CCL.CodeId = C.Id
                 ORDER BY C.Code
                 FOR XML PATH('')), 1, 1, '') AS [Chapter]
From Codes C

This worked spot on.

Thank you.