How to join values in 2 columns with 1 column?

Suppose I have 2 tables like:

table_a 
student teacher
A       Z
B       Z
C       Z
    
    
table_b 
id  person
1   A
2   B
3   C
4   Z

I'm looking for an output of:

table_c 
student_id  teacher_id
1           4
2           4
3           4

I've been trying to solve this for a while, but can't figure out a simple method and believe I'm overthinking this. I'm confused how it's possible to join values in student and teacher with the person to get my output. I usually attach code that I've tried to my posts but am honestly drawing blanks here.

CodePudding user response：

You can try to use two correlated-subqueries to make it.

SELECT (SELECT b.id  FROM table_b b WHERE b.person = a.student LIMIT 1) student_id,
       (SELECT b.id  FROM table_b b WHERE b.person = a.teacher LIMIT 1) teacher_id
FROM table_a  a