多对多查询
表:
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
CREATE TABLE `stud_course` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`stud_id` int(11) DEFAULT NULL,
`cou_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
CREATE TABLE `course` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
查询:
SELECT
s.*, c. NAME
FROM
student AS s
INNER JOIN stud_course AS sc ON s.id = sc.stud_id
INNER JOIN course AS c ON sc.cou_id = c.id
WHERE
c. NAME = '数学'