SQL Two Tables, One Result with extras

Question

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).

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.

something like -

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

Answer 1

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

Answer 2

And perhaps a count() for attendance?

Answer 3

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

Answer 4

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

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)