I have two tables one is teachers another is subjects and I need to link the subjects to the teachers , thats an easy one but the problem is that a single teacher can have multiple subjects.Thus I need a kind of array for that, so that when I do my queries with python it returns an array or should I say a 'tuple of tuple of tuples'. So can someone help me to solve that?
Database Architecture is most important thing to decide. Here seems 2 approach one is decided by you or other to make a mapping table.
For your Approach:-
id teacher_name Subject 1 XYZ 1,5,6,7
SELECT teacher_name, subject_name FROM subject s INNER JOIN teacher t on FIND_IN_SET(s.id,t.subject)
Other is make a mapping table:-
teacher_id subject_id 1 1 1 5 1 7
SELECT teacher_name, subject_name FROM mapping m INNER JOIN subject s on m.subject_id = s.id INNER JOIN teacher t on m.teacher_id = t.id