Relation n:m

SET FOREIGN_KEY_CHECKS=0;
 
-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `year` INT(11) NOT NULL,
  `letter` VARCHAR(10) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES ('1', '1', 'a');
INSERT INTO `class` VALUES ('2', '1', 'b');
INSERT INTO `class` VALUES ('3', '1', 'c');
INSERT INTO `class` VALUES ('4', '2', 'a');
INSERT INTO `class` VALUES ('5', '2', 'b');
INSERT INTO `class` VALUES ('6', '2', 'c');
INSERT INTO `class` VALUES ('7', '3', 'a');
INSERT INTO `class` VALUES ('8', '3', 'b');
INSERT INTO `class` VALUES ('9', '3', 'c');
 
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
  `class_id` INT(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT;
 
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', 'Aomori Kouji', '1');
INSERT INTO `student` VALUES ('2', 'Kimura Aoi', '3');
 
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', 'Suzuki Minami');
INSERT INTO `teacher` VALUES ('2', 'Aoi Kaori');
INSERT INTO `teacher` VALUES ('3', 'Ishikawa Kyousuke');
 
-- ----------------------------
-- Table structure for teacher_class_map
-- ----------------------------
DROP TABLE IF EXISTS `teacher_class_map`;
CREATE TABLE `teacher_class_map` (
  `teacher_id` INT(11) NOT NULL,
  `class_id` INT(11) NOT NULL,
  PRIMARY KEY (`teacher_id`,`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT;
 
-- ----------------------------
-- Records of teacher_class_map
-- ----------------------------
INSERT INTO `teacher_class_map` VALUES ('1', '1');
INSERT INTO `teacher_class_map` VALUES ('1', '2');
INSERT INTO `teacher_class_map` VALUES ('1', '4');
INSERT INTO `teacher_class_map` VALUES ('2', '1');
INSERT INTO `teacher_class_map` VALUES ('2', '4');
INSERT INTO `teacher_class_map` VALUES ('2', '6');
INSERT INTO `teacher_class_map` VALUES ('3', '1');
INSERT INTO `teacher_class_map` VALUES ('3', '2');
INSERT INTO `teacher_class_map` VALUES ('3', '3');

SELECT
    teacher.`name`,
    CONCAT(class.`year`, class.`letter`) AS class_name
FROM teacher_class_map AS tcmap
INNER JOIN teacher ON teacher.id = tcmap.teacher_id
INNER JOIN class ON class.id = tcmap.class_id

SELECT
    teacher.`name` AS teacher_name,
    CONCAT(class.`year`, class.`letter`) AS class_name,
    student.`name` AS student_name
FROM teacher_class_map AS tcmap
INNER JOIN teacher ON teacher.id = tcmap.teacher_id
INNER JOIN class ON class.id = tcmap.class_id
INNER JOIN student ON student.class_id = class.id