先把表结构给出
CREATE TABLE `course` (
`Course_ID` int(11) NOT NULL AUTO_INCREMENT,
`Course_Name` varchar(255) NOT NULL,
PRIMARY KEY (`Course_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;/*Data for the table `course` */insert into `course`(`Course_ID`,`Course_Name`) values (1,'ENSE477'),(2,'ENSE473'),(3,'ENGG401'),(4,'ENGG405'),(5,'ENGG407');/*Table structure for table `sc` */DROP TABLE IF EXISTS `sc`;CREATE TABLE `sc` (
`Student_ID` int(11) NOT NULL,
`Course_ID` int(11) NOT NULL,
KEY `Student_ID` (`Student_ID`),
KEY `Course_ID` (`Course_ID`),
CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`Student_ID`) REFERENCES `student` (`Student_ID`),
CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`Course_ID`) REFERENCES `course` (`Course_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*Data for the table `sc` */insert into `sc`(`Student_ID`,`Course_ID`) values (200268900,1),(200268900,3),(200268900,4),(200224523,1),(200224523,3),(200224523,4),(200224523,5),(200255555,1),(200255555,3),(200255555,5),(200268901,1),(200268901,2),(200268901,3),(200268901,5),(200268902,1),(200268902,2),(200268902,3),(200268902,5),(200268903,1),(200268903,2),(200268903,3),(200268903,5),(200268904,1),(200268904,2),(200268904,3);我想实现的效果如下:
Course_ID Course_Name Student_ID result
3 ENGG401 200224523 yes
4 ENGG405 200224523 yes
5 ENGG407 200224523 yes
1 ENSE477 200224523 yes
2 ENSE473 NULL no也就是根据学生ID列出此学生还未注册的课程
CREATE TABLE `course` (
`Course_ID` int(11) NOT NULL AUTO_INCREMENT,
`Course_Name` varchar(255) NOT NULL,
PRIMARY KEY (`Course_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;/*Data for the table `course` */insert into `course`(`Course_ID`,`Course_Name`) values (1,'ENSE477'),(2,'ENSE473'),(3,'ENGG401'),(4,'ENGG405'),(5,'ENGG407');/*Table structure for table `sc` */DROP TABLE IF EXISTS `sc`;CREATE TABLE `sc` (
`Student_ID` int(11) NOT NULL,
`Course_ID` int(11) NOT NULL,
KEY `Student_ID` (`Student_ID`),
KEY `Course_ID` (`Course_ID`),
CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`Student_ID`) REFERENCES `student` (`Student_ID`),
CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`Course_ID`) REFERENCES `course` (`Course_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*Data for the table `sc` */insert into `sc`(`Student_ID`,`Course_ID`) values (200268900,1),(200268900,3),(200268900,4),(200224523,1),(200224523,3),(200224523,4),(200224523,5),(200255555,1),(200255555,3),(200255555,5),(200268901,1),(200268901,2),(200268901,3),(200268901,5),(200268902,1),(200268902,2),(200268902,3),(200268902,5),(200268903,1),(200268903,2),(200268903,3),(200268903,5),(200268904,1),(200268904,2),(200268904,3);我想实现的效果如下:
Course_ID Course_Name Student_ID result
3 ENGG401 200224523 yes
4 ENGG405 200224523 yes
5 ENGG407 200224523 yes
1 ENSE477 200224523 yes
2 ENSE473 NULL no也就是根据学生ID列出此学生还未注册的课程
from sc where Student_ID=200224523) b on a.Course_ID=b.Course_ID