MS Access SQL，显示学生之前未选择的课程 [英] MS Access SQL that shows courses NOT TAKEN by student before

问题描述

我尝试了许多方法，我怀疑NOT EXIST可能会有所帮助，但是我不确定如何实现SQL语句.有人帮我吗?

I have tried many ways, and i suspect NOT EXIST may help, but i am not sure how to implement the SQL statement. Someone help me please?

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

I have three tables in MS Access database.

Students (Student Name, Email)
Courses (Course Name, Fees)
Registration (Course Name, Student Name)

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

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 |基本编辑
Adrian |基本Flash
Adrian |高级Flash
Adrian |高级编辑

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

(James参加了所有基础"课程，而Adrian没有参加过基础"和高级"课程)

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

推荐答案

该想法是创建学生和课程的所有组合.然后对Registrations执行left join. 不匹配的所有内容都不会作为课程.

The idea is to create all combines of students and courses. Then do a left join to Registrations. Anything that does not match is a course not taken.

访问语法与其他数据库有点不同.我认为以下是这种查询的Access语法:

Access syntax is a bit different from other databases. I think the following is the Access syntax for this type of query:

select s.studentName, c.CourseName
from ((select studentName
       from students
      ) s,
      (select CourseName
       from courses
      ) c
     ) left join
     Registration r
     on r.StudentName = s.StudentName and
        r.CourseName = s.CourseName
where r.StudentName is NULL;

我认为可能必须正确订购Parens，以便Access能够理解联接.无论如何，下面的方法应该起作用. driver子查询获取学生和课程的所有组合，然后用于left join:

I think the parens might have to be ordered correctly for Access to understand the joins. In any case, the following should work. The driver subquery gets all combinations of students and courses, which is then used for the left join:

select driver.StudentName, driver.CourseName
from (select distinct StudentName, CourseName
      from students, courses
     ) driver left join
     Registration r
     on r.StudentName = driver.StudentName and
        r.CourseName = driver.CourseName
where r.StudentName is NULL;

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