MySQL get multiple items from a table as a input for a single field of another table
Question: 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?

Thanks


Answer: 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
    

Query:-

    
    
    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
    

Query:-

    
    
    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
    

