mysql - SQL查询: How to validate the prerequisite for courses that finished and how to find time conflict of courses?

Question

我正在尝试编写查询来建议学生注册哪些类(class)。该查询将选择合适的类(class)并验证 1) 他们已完成的类(class)。以及他们还需要参加哪些类(class) 2) 要完成的先决类(class) .3) 验证时间冲突。以便为他推荐最好的类(class)。

我做了这些表并连接它们，但是连接操作不起作用。什么语法是正确的？如果没有先决条件怎么办？我将如何检查？一些先决条件是针对高级或初级的，是否需要单独的表格？

错误 1064 (42000):您的 SQL 语法有错误；检查与您对应的手册MYSQL 服务器版本，用于在 'studyplan sp 附近使用正确的语法 (t.std_id=sp.std_is)左外连接先决条件 p on (p.preid = c.' at line 3

     select c.*
    from std t
   inner join schedule22  c studyplan sp
   on (t.std_id=sp.std_id)
   left outer join prerequsit p
   on (p.preid=c.courseid)
   inner join schedule22 c
   on (c.courseid=p.courseid)
   where t.std=1 AND
   sp.complated='No' AND
   sp.passed='No' AND
   p.preid=courseid;   

学生

enter code here

  std_id       username    pass   fname     email


   1             hjh                154     jdf         example@live.com



    Studyplan

  Courseid   code  `prerequisite        std_id    completed    passed
   2       UNS 100   No Prerequisite     1         Y             Y
     3,   'ENG 100',   'No Prerequisite',    1, 'Y', 'Y'),
     5,  'MTT 101',   'MTG 100',   1, 'Y', 'Y'),
     6, 'MTT 202',   'MTT 101', 1, 'Y', 'N'),
    (7, 'STT 100', 'No Prerequisite', 1, 'N', 'N'),
    (8, 'MTT 102','MTT 101', 1, 'N', 'N'),
    (9, 'ENG 200','english1', 1, 'N', 'N'),
    (10, 'OE1',3, 'NULL', 1, 'N', 'N'),
    (11, 'ENG 201','ENG 200', 1, 'N', 'N'),
    (12, 'CSC 302', 'MTT 202', 1, 'N', 'N'),
    (13, 'STT 201',, 'STT 100', 1, 'N', 'N'),
    (15, 'CSC 201','MTT 101 or MTT 102', 1, 'N', 'N'),
    (16, 'CSC 202', 'CSC 201', 1, 'N', 'N'),
    (17, 'PSY 201', 'ENG 100 + UNS 100', 1, 'N', 'N'),
    (18, 'NSC 201', 'No Prerequisite', 1, 'N', 'N'),
    (19, 'CSC 307', 'CSC 201', 1, 'N', 'N'),
   (20, 'CSC 301','CSC 202', 1, 'N', 'N'),
   (21, 'ITE 390', 'Junior Level', 1, 'N', 'N'),
   (22, 'CSC 305', 'Junior Level', 1, 'Y', 'Y'),
   (23, 'ITE 305', ' 'Junior Level', 1, 'Y', 'Y'),
   (24, 'ITE 414', ', 'junior Level', 1, 'Y', 'Y'),
   (25, 'CSC 308',  'CSC 301', 1, 'N', 'N'),
   (26, 'ITE 402', 'CSC 305', 1, 'N', 'N'),
  (27, 'CSC 311', 'CSC 201', 1, 'N', 'N'),
  (28, 'ITE 422',  'CSC 305', 1, 'N', 'N'),
 (29, 'CIS 401',  'CSC 302', 1, 'N', 'N'),
 (30, 'ITE 409',  'Senior Level', 1, 'N', 'N'),
 (31, 'CIS 401',  'CSC 302', 1, 'N', 'N'),
 (32, 'CSC 401', 'ITE 305', 1, 'N', 'N'),
 (33, 'ITE 409', 'Null', 1, 'N', 'N'),
  (34, 'ITE 408',  'CSC 305', 1, 'N', 'N')

时间表

enter code here

 semester`, `courseid`, `coursecode`, `section`,`date`, `time`, `,`sch_id`) 
  ('fall', 9, 'ENG 100', 51,'MoWe', '1:45PM-3:15PM', 'staff',1),
  ('fall', 16, 'CSC202', 51, 'Mo-We',' 1:45PM-3:15PM', 'staff',1),
  ('fall', 26, 'ITE402', 51, 'Tu','10:30-12pm', 'staff',1),
  ('fall', 6, 'MTT 202', 51,'Su-Tu', '12:00-2:00PM', 'staff',1),
  ('fall', 8, 'MTT 102', 51','SuTu',' 12:00-2:00PM', 'staff',1),
  ('fall', 12, 'CSC 302', 51,'Mo-We',' 10:00-12:00PM', 'staff',1),
  ('fall', 15, 'CSC 201', 52,'Mo-We',' 10:00-12:00PM', 'staff',1),
  ('fall', 21, 'ITE 390', 51, 'Su-Tu',' 12:00-2:00PM', 'staff',1),
  ('fall', 5, 'MTT 101', 51, 'Su',' 4:00PM -7:00PM', 'staff',1),


   ('fall', 28, 'ITE 422', 51, Su-Tu',' 12:00-2:00PM', 'staff',1);

先决条件`

enter code here

    (`courseid`, `preid`) VALUES
       (5, 1,),
       (6, 2),
       (8, 3),
      (9, 4),
       (11, 5),
      (12, 6),
      (13, 7),
      (14, 8),
     (15, 9),
      (16, 10),
     (17, 11),
    (18, 12),
     (19, 13),
    (20, 14),
    (21, 21),
    (22, 22),
   (23, 23),
    (24, 24),
   (25, 20),
 (26, 22),
  (27, 25),
  (28, 22),
  (29, 12),
  (30, 30),
 (32, 23),
  (34, 22,),
  (35, 12),
  (36, 22),
  (37, 3),

Answer 1

您的查询在 from 子句中包含两次 schedule22 c 。这是一个错误。可能还有更多。