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.