MySQL - How can I get names of all students that were taught by teacher with name John?

Question

How can I get all students names that were taught by the teacher with name John?

student

id
name

student_has_teacher

teacher_id
student_id

teacher

id
name

Answer 1

You have to use Inner Joins. An Inner Join creates a new result table by combining column values of two tables based upon a join-predicate. Your query should probably look something like this:

SELECT      student.name
FROM        student
INNER JOIN  student_has_teacher ON (student_has_teacher.student_id = student.id)
INNER JOIN  teacher ON (teacher.id = student_has_teacher.teacher_id)
WHERE       teacher.name = 'John';

Answer 2

The first query below assumes there may be duplicate records in student_has_teacher, and will avoid causing duplicate results even if that is the case:

select s.id, s.name
from student s
where s.id in (
    select sht.student_id 
    from student_has_teacher sht
    inner join teacher on sht.teacher_id = t.id
    where t.name = 'John'
)

If there are not duplicates in student_has_teacher, then you can use joins, like this:

select s.id, s.name
from student s
inner join student_has_teacher sht on s.id = sht.student_id
inner join teacher on sht.teacher_id = t.id
where t.name = 'John'

Answer 3

Sounds like homework. Presumably you have a table of students and teachers. How about you look into the JOIN and WHERE statements?

Answer 4

SELECT s.name
FROM student s 
    INNER JOIN student_has_teacher st ON s.id = st.student_id
    INNER JOIN teacher t ON st.teacher_id = t.id
WHERE t.name = 'John'

Answer 5

If joins seem daunting, try sub queries which are usually easier to read:

SELECT id, name
FROM student
WHERE id IN (
    SELECT student_id
    FROM student_has_teacher
    WHERE teacher_id = (
        SELECT id
        FROM teacher_id
        WHERE name = 'John'
    )
)

Answer 6

Hi, the following query will give you the desired result for sure.

SELECT st.name
FROM student AS st
LEFT JOIN student_has_teacher AS stte ON st.id = stte.student_id
LEFT JOIN teacher AS te ON stte.teacher_id = te.id
WHERE te.name = "John"