Logic Question

Ok so I have a little problem. I dont want code or anything I just
want a little help with the logical steps to take to complete the
process.
Little background, we have students who come and take 10 courses to get
this certification. All 10 courses MUST be completed to get certified.
First I have three tables. tblStudent and tblCourse and
tblCourseCompleted.
tblStudent(StudentID) obviously contains all students enrolled,
tblCourse(CourseID) lists all courses offered(Only 10 courses
currently) and tblCourseCompleted lists the Student ID and Course ID
and Date the Course was completed.
So based off tblCourseCompleted i have a complete list of all the
students who have completed various courses.
BUT, What I want to do is generate a report that lists the which
courses the students HAVE NOT taken. So it needs to list Each student
and every course hes missing. So if student A is missing 4 courses, it
will list student A four times with each course he has not yet
completed.
So basically i need the EXACT opposite of tblCourseCompletions.
Can anyone help me with the logic of some how generating this type of
report?Sometimes it is simpler to explain logic with an example:
SELECT *
FROM tblStudent as S
CROSS JOIN tblCourse as C
WHERE NOT EXISTS
(select * from tblCourseCompleted as X
where X.course = C.course
and X.student = S.student)
The cross join gives every possible combination of student and course.
The NOT EXISTS correlated subquery eliminates those that have been
completed.
Roy Harvey
Beacon Falls, CT
On 12 Jul 2006 08:55:23 -0700, "wmureports" <justin.a.moran@.gmail.com>
wrote:
>Ok so I have a little problem. I dont want code or anything I just
>want a little help with the logical steps to take to complete the
>process.
>Little background, we have students who come and take 10 courses to get
>this certification. All 10 courses MUST be completed to get certified.
>First I have three tables. tblStudent and tblCourse and
>tblCourseCompleted.
>tblStudent(StudentID) obviously contains all students enrolled,
>tblCourse(CourseID) lists all courses offered(Only 10 courses
>currently) and tblCourseCompleted lists the Student ID and Course ID
>and Date the Course was completed.
>So based off tblCourseCompleted i have a complete list of all the
>students who have completed various courses.
>BUT, What I want to do is generate a report that lists the which
>courses the students HAVE NOT taken. So it needs to list Each student
>and every course hes missing. So if student A is missing 4 courses, it
>will list student A four times with each course he has not yet
>completed.
>So basically i need the EXACT opposite of tblCourseCompletions.
>Can anyone help me with the logic of some how generating this type of
>report?|||Try,
select t1.*
from
(
select a.StudentID, b.CourseID
from tblStudent as a cross join tblCourse as b
) as t1
where not exists(
select *
from tblCourseCompleted as t2
where t2.StudentID = t1.StudentID and t2.CourseID = t1.CourseID
)
AMB
"wmureports" wrote:
> Ok so I have a little problem. I dont want code or anything I just
> want a little help with the logical steps to take to complete the
> process.
> Little background, we have students who come and take 10 courses to get
> this certification. All 10 courses MUST be completed to get certified.
> First I have three tables. tblStudent and tblCourse and
> tblCourseCompleted.
> tblStudent(StudentID) obviously contains all students enrolled,
> tblCourse(CourseID) lists all courses offered(Only 10 courses
> currently) and tblCourseCompleted lists the Student ID and Course ID
> and Date the Course was completed.
> So based off tblCourseCompleted i have a complete list of all the
> students who have completed various courses.
> BUT, What I want to do is generate a report that lists the which
> courses the students HAVE NOT taken. So it needs to list Each student
> and every course hes missing. So if student A is missing 4 courses, it
> will list student A four times with each course he has not yet
> completed.
> So basically i need the EXACT opposite of tblCourseCompletions.
> Can anyone help me with the logic of some how generating this type of
> report?
>|||From a logic point of view , you want to SELECT courses per student NOT IN
the tblCoursesCompalted.
The way you'be described the first report would be using an INNER JOIN , i.e
only display the record , if it exists as a
relationship in the tblCourseCompleted . On a logic level, you need to find
the set that doesn't have a relationship in that table.
You could use a LEFT OUTER JOIN and then put a condition on the WHERE
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"wmureports" <justin.a.moran@.gmail.com> wrote in message
news:1152719723.696320.29420@.75g2000cwc.googlegroups.com...
> Ok so I have a little problem. I dont want code or anything I just
> want a little help with the logical steps to take to complete the
> process.
> Little background, we have students who come and take 10 courses to get
> this certification. All 10 courses MUST be completed to get certified.
> First I have three tables. tblStudent and tblCourse and
> tblCourseCompleted.
> tblStudent(StudentID) obviously contains all students enrolled,
> tblCourse(CourseID) lists all courses offered(Only 10 courses
> currently) and tblCourseCompleted lists the Student ID and Course ID
> and Date the Course was completed.
> So based off tblCourseCompleted i have a complete list of all the
> students who have completed various courses.
> BUT, What I want to do is generate a report that lists the which
> courses the students HAVE NOT taken. So it needs to list Each student
> and every course hes missing. So if student A is missing 4 courses, it
> will list student A four times with each course he has not yet
> completed.
> So basically i need the EXACT opposite of tblCourseCompletions.
> Can anyone help me with the logic of some how generating this type of
> report?
>|||OK that worked PERFECTLY! thank you very much :)
Roy Harvey wrote:
> Sometimes it is simpler to explain logic with an example:
> SELECT *
> FROM tblStudent as S
> CROSS JOIN tblCourse as C
> WHERE NOT EXISTS
> (select * from tblCourseCompleted as X
> where X.course = C.course
> and X.student = S.student)
> The cross join gives every possible combination of student and course.
> The NOT EXISTS correlated subquery eliminates those that have been
> completed.
> Roy Harvey
> Beacon Falls, CT
> On 12 Jul 2006 08:55:23 -0700, "wmureports" <justin.a.moran@.gmail.com>
> wrote:
> >Ok so I have a little problem. I dont want code or anything I just
> >want a little help with the logical steps to take to complete the
> >process.
> >
> >Little background, we have students who come and take 10 courses to get
> >this certification. All 10 courses MUST be completed to get certified.
> >
> >First I have three tables. tblStudent and tblCourse and
> >tblCourseCompleted.
> >
> >tblStudent(StudentID) obviously contains all students enrolled,
> >tblCourse(CourseID) lists all courses offered(Only 10 courses
> >currently) and tblCourseCompleted lists the Student ID and Course ID
> >and Date the Course was completed.
> >
> >So based off tblCourseCompleted i have a complete list of all the
> >students who have completed various courses.
> >
> >BUT, What I want to do is generate a report that lists the which
> >courses the students HAVE NOT taken. So it needs to list Each student
> >and every course hes missing. So if student A is missing 4 courses, it
> >will list student A four times with each course he has not yet
> >completed.
> >
> >So basically i need the EXACT opposite of tblCourseCompletions.
> >
> >Can anyone help me with the logic of some how generating this type of
> >report?