Kuntal Majumder Kuntal Majumder - 2 months ago 6x
MySQL Question

MySQL get multiple items from a table as a input for a single field of another table

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