SQL语句，显示学生之前未修过的课程 [英] SQL statement that shows Courses NOT taken before by Students

问题描述

我在MS Access数据库中有三个表.

I have three tables in MS Access database.

1. 学生(学生姓名，电子邮件)

1. Students (Student Name, Email)

1. 课程(课程名称，费用)
2. 注册(课程名称，学生姓名)

我现在想创建一个查询，该查询可以向我显示每个学生以前没有参加过的所有课程.这将使我的注册管理员可以要求他们进行更多的班级注册.

I want to now create a Query that can show me all classes that each student HAS NOT taken before. This will allow my Registration Manager to pursue them to perform more class registration.

如何在MS ACCESS中轻松完成此操作?结果查询应为:

How can I easy do this in MS ACCESS? The resulting query should be:

James |高级Flash |詹姆斯|高级编辑|Adrian |基本编辑|阿德里安|基本Flash |阿德里安|高级Flash |Adrian |高级编辑|

James|Advanced Flash| James|Advanced Editing| Adrian|Basic Editing| Adrian|Basic Flash| Adrian|Advanced Flash| Adrian|Advanced Editing|

(詹姆斯参加了所有基础"课程，而阿德里安没有参加过基础"和高级"课程)

(James have taken all 'Basic' classes where else Adrian has not taken 'Basic' and 'Advanced' classes)

推荐答案

进行此类查询时，拥有一个包含所有学生和课程组合的 driver 表会有所帮助.然后，将其加入注册，并选择不匹配的行:

When doing this type of query, it helps to have a driver table that has all combinations of students and courses. Then, join this to the registrations, and choose the rows where there is no match:

select driver.*
from (select s.StudentName, c.CourseName
      from Students as s,
           Courses as c
     ) as driver left join
     Registration as r
     on r.StudentName = driver.StudentName and
        r.CourseName = driver.CourseName
where r.StudentName is NULL;

这篇关于SQL语句，显示学生之前未修过的课程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！