Get one row from table based off another

Question

I have two tables, users and teaching

The users table contains both teachers and students. The teaching table contains the relation between the two.

I want to be able to get the student based off who "teaches" them.

Both teachers and students have an id, and I would have the teachers id to look up in the teaching table to find the students they teach.

Any ideas as to how this is done? I would like it to return the id of the student.

I've tried doing a simple join statement, but this would fetch the teacher, not the student.

Thanks in advance

Answer 1

Something like that?

SELECT users.id FROM users INNER JOIN teaching on users.id = teaching.userId WHERE teaching.teacherId = '';

Answer 2

You just need to join the users table again to the teaching table. Just make sure in one join you are using the teachers' ids and in the other the students' ids.

You can join the same table multiple times and that is totally legal in MySQL