I have a N-N table, lets say as an example Teachers and Subjects: There is a Teacher table as follows: There is a Subject table as follows: And there is a TeacherTeachesSubject as follows: Only that each table has more than 10000 entries. I need to make a query which returns me something like this: What I am doing right

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

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:

SELECT * FROM Teachers;
Save the results in an array.

Iterate over the array executing this query in each loop:

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

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

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

Advertisement

Answer