MySQL statement to get a column's value from all rows where another column's value = a specified value

Could someone开发者_开发技巧 please show me a MySQL statement that will get the value of column student, in all rows where the value of column teacher is equal to charles?

If this info is in the same table, then

select student from table_name where teacher = 'charles'

If you have 3 tables like it should be (teachers, students and the M:N table for teachers_students), please comment as then you'd need a join.