How to write many to many sql script?

Question

SQL

How to write many to many sql script?

students

id | name

classes

id | name

student_classes

id | student_id | class_id

The result should be like this

id | name | classes

1  | Bob  | Class 1, Class 2
2  | Sara | Class 2
3  | Jon  | -

Answer 1

LEFT JOIN and GROUP_CONCAT if mysql
https://www.mysqltutorial.org/mysql-group_concat/

SELECT s.id, s.name, GROUP_CONCAT(DISTINCT c.name SEPARATOR ', ') as classes 
FROM students s
LEFT JOIN student_classes sc ON sc.student_id = s.id
LEFT JOIN classes c ON c.id = sc.class_id
GROUP BY s.name
ORDER BY s.id ASC;

Similar for PostgreSQL, only the STRING_AGG function https://www.postgresqltutorial.com/postgresql-aggr...