Skip to content
Advertisement

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:

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 now is:

  1. SELECT * FROM Teachers;

  2. Save the results in an array.

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

  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.

Advertisement

Answer

With SQL Server 2017, use STRING_AGG() :

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement