Mutual student

mutual_students_mysql.md

Structure

CREATE TABLE teacher(
	id INT(10) PRIMARY KEY NOT NULL AUTO_INCREMENT,
	name VARCHAR(100) NOT NULL
);

CREATE TABLE student(
	id INT(10) PRIMARY KEY NOT NULL AUTO_INCREMENT,
	name VARCHAR(100) NOT NULL
);

CREATE TABLE teacher_student (
	teacher_id INT(10) NOT NULL, 
	student_id INT(10) NOT NULL
);

Teachers with more than 5 students

SELECT t.* FROM teacher t JOIN teacher_student ts ON ts.teacher_id = t.id
GROUP BY t.id HAVING COUNT(ts.student_id) > 5;

Two first teachers with maximum mutual students

SELECT t.name, ts2.teacher_id as mutual_id, COUNT(ts1.student_id) as mutual_count
FROM teacher_student ts1
  INNER JOIN
    teacher_student ts2 ON ts1.teacher_id != ts2.teacher_id AND ts1.student_id = ts2.student_id
  JOIN teacher t ON ts1.teacher_id = t.id
GROUP BY ts1.teacher_id, ts2.teacher_id ORDER BY mutual_count DESC LIMIT 2;