SQL两个表，一个带附加功能的结果

I have two tables. The first Holds a list of classes with basically an ClassID and a ClassName. The second Holds a list of classes that a pupil does attend using ClassID and PupilID (PupilID refers to a third table).

我有两张桌子。第一个包含基本上具有ClassID和ClassName的类列表。第二个保存学生使用ClassID和PupilID参加的课程列表(PupilID指的是第三个表)。

Is it possible to return for a given pupil a list of ALL Classes irrespective of attendance yet indicate the classes they do attend using MS SQL query. I have read about SQL CASE and IF and even something about creating and assigning variables in queries but none of the examples indicate more than one table.

是否可以为给定的学生返回所有课程的列表,无论出席情况如何,但是使用MS SQL查询指示他们参加的课程。我已经阅读了有关SQL CASE和IF的内容,甚至还有关于在查询中创建和分配变量的内容,但没有一个示例表明有多个表。

something like -

就像是 -

|ClassName | Attended
|   1      |    0 
|   2      |    0 
|   3      |    1 
|   4      |    0

4 个解决方案

#1

Try this - appologies to Degan for stealing your example and changing it.

试试这个 - 向Degan道歉,窃取你的例子并改变它。

SELECT t.ID, 
       t.Class_ID, 
       t.Pupil_ID, 
       c.Class_ID AS Expr1, 
       c.Class_Name, 
       c.Class_Day, 
       c.Class_OrderView 
FROM Classes AS c
LEFT OUTER JOIN timetable as t 
       ON (c.Class_ID = t.Class_ID) AND (t.Pupil_ID = MyPupil)

#2

Sounds like homework to me. Try looking up LEFT OUTER JOIN in your documentation.

听起来像是家庭作业。尝试在文档中查找LEFT OUTER JOIN。

#3

And perhaps a count() for attendance?

也许是一个计数()出席?

#4

SELECT t.ID, 
       t.Class_ID, 
       t.Pupil_ID, 
       c.Class_ID AS Expr1, 
       c.Class_Name, 
       c.Class_Day, 
       c.Class_OrderView 
FROM timetable AS t
       LEFT OUTER JOIN classes as c 
       ON (t.Class_ID = c.Class_ID) AND (t.Pupil_ID = MyPupil)

The problem is that pesky WHERE when doing a JOIN

问题是在做JOIN时麻烦的WHERE

#1