Selecting all foreign rows for each row useing a N-N table

Question

I have a N-N table, lets say as an example Teachers and Subjects:

There is a Teacher table as follows:

    +---------------+-----------------------+
    |    Id         | Name                  |
    +---------------+-----------------------+
    | 1             | John Williams         |
    | 2             | Erika Miller          |
    +---------------+-----------------------+

There is a Subject table as follows:

    +---------------+-----------------------+
    |    Id         | Name                  |
    +---------------+-----------------------+
    | 1             | Music                 |
    | 2             | Art                   |
    | 3             | Languages             |
    | 4             | Geography             |
    +---------------+-----------------------+

And there is a TeacherTeachesSubject as follows:

    +---------------+-----------------------+
    |  TeacherId    |        SubjectId      |
    +---------------+-----------------------+
    | 1             | 1                     |
    | 1             | 2                     |
    | 1             | 3                     |
    | 2             | 3                     |
    | 2             | 4                     |
    +---------------+-----------------------+

Only that each table has more than 10000 entries.

I need to make a query which returns me something like this:

+---------------+-----------------------+
|    Teacher    | SubjectsOfThatTeacher |
+---------------+-----------------------+
| John Williams | Music, Art, Languages |
| Erika Miller  | Languages, Geography  |
+---------------+-----------------------+

What I am doing right now is:

1. SELECT * FROM Teachers;

2. Save the results in an array.

3. Iterate over the array executing this query in each loop:

   SELECT Name 
   FROM Subjects inner join "everything" 
   WHERE TeacherTeachesSubject.TeacherId = actualteacherid;
   

4. Save the results as String, separated with commas.

I'm making 10000 queries every time I want to select all data from the table.

Do you know any way of making this work efficiently? I don't really need commas, I just need it to be in a String to show in a HTML column as a String.

Answer 1

With SQL Server 2017, use STRING_AGG() :

SELECT t.Name, STRING_AGG(s.Name, ', ') SubjectsOfThatTeacher
FROM Teacher t
INNER JOIN TeacherTeachesSubject tts ON tts.TeacherId = t.Id
INNER JOIN Subject s ON s.Id = tts.SubjectId
GROUP BY t.Id, t.Name

Answer 2

For pre-SQL Server 2017, there's the FORXML and STUFF hack:

SELECT t.Name, SubjectsOfThatTeacher = STUFF(
                                   (SELECT ',' + s.Name 
                                    FROM TeacherTeachesSubject tts 
                                    INNER JOIN Subject s ON s.Id = tts.SubjectId
                                    WHERE tts.TeacherId = t.Id
                                    FOR XML PATH(''))
                                    ,1,1,'')
FROM Teacher t
GROUP BY t.Id, t.Name

Answer 3

Use GROUP_CONCAT aggregates for pre SQL Server 2017. Available at Github

Download GROUP_CONCAT