Can you make a query to display a join table with one non-atomic column?

Question

When you make queries to join tables is there a way to make one column non-atomic?
For example:
two tables- one for teachers (id, name etc) and one for classes (taughtById, title, hours etc)

a teacher that teaches many classes will have multiple rows for each class. Can you make a query to display a join table for the tables classes and teachers to have a column with a list of classes the teacher holds instead? Even just temporary for readability?

Answer 1

You can use a connector table to connect the two tables. The connector table should reference ID from the two tables, and hold only the ID of the teacher and class.

Teacher:
id, name, etc.

Class:
id, title, hours, etc.

Teacher_Class
teacher_id, class_id

This way you can store multiple pairs of teacher's and class' ID in `Teacher_Class` and work the query out 👍