Outer join - need to always show subtable data

[jsolutions]

Basically, I have three tables:
tblStudents - StudentId and name of student
tblclasses - Classid, name of class, description of class
tblclassescompleted - studentid, classid, dateattended

Assume for the moment that there are 3 classes that each student is required to take (for now that is all that is in the classes table).

I am trying to create an output that shows each student's name, class name, description and date attended (or blank date if they have not attended). This is not a problem if they have actually attended the class.

The issue comes in when they have not taken the class (IE, dateattended is null). I want to be able to see the class name/desciprtion even if they have not yet attended the class so that I can tell what classes they still need to take.

I keep coming up with ambigous outer joins when I try this.

Any ideas would be greatly appreciated.

 

[LKBrwnDBA]

Is this a homework assigment? Sure looks like one.

Post your code and maybe someone will take pitty and help you.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb