My SQL skills are quite limited. I'm in my second year of computer science at a technical college. I'm building a windows forms application that will allow the BAS director at my college to keep track of students and their progress throughout the courses. I have complete control over the database design so if you think of a way to help me reach a solution that would involve tweaking the database that is a possibility.

I'm trying to select all of the Students who do not have an EnrollmentStatus of 3 in all of the Courses that have a CreditSection of 1. There are 12 courses with a CreditSection of 1

The tables I'm using look like this:

I can think of a few ways to get my solution in speech, but can't seem to write them SQL:

SELECT * FROM Students WHERE each student has 12 entries in CourseEnrollment AND
CourseEnrollment.EnrollmentStatus = 3 AND Courses.CreditSection = 1

or

SELECT * FROM Students WHERE Courses.CourseID 1 thru 12 EXIST in
CourseEnrollment for each student AND CourseEnrollment.EnrollmentStatus = 3

I can get to the desired solution using this mess below, but as I check for students who have completed 4 years worth of courses... this query becomes ridiculously long and probably mega resource consuming.

This query selects students who are not in the list of students who have completed one or more the given courses:

SELECT DISTINCT s.* FROM Students s
WHERE s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 1 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 2 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 3 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 4 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 5 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 6 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 7 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 8 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 9 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 10 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 11 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 12 AND ce.EnrollmentStatus = 3) OR
s.StudentID NOT IN (SELECT ce.StudentID FROM CourseEnrollment ce WHERE ce.CourseID = 13 AND ce.EnrollmentStatus = 3)

My goal is to figure out how to write this query in SQL and then convert it to LINQ which is ultimately what I need. If anyone can help with either part of this I would be grateful.

I've converted the above to LINQ and it looks just as horrendous:

var query =
                from student in datStudents.Students.AsEnumerable<dsStudentManager.StudentsRow>()
                where !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 1 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 2 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 3 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 4 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 5 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 6 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 7 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 8 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 9 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 10 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 11 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 12 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID) ||
                !(from ce2 in datStudents.CourseEnrollment.AsEnumerable<dsStudentManager.CourseEnrollmentRow>() where ce2.CourseID == 13 && ce2.EnrollmentStatus == 3 select ce2.Field<int>("StudentID")).Contains<int>(student.StudentID)
                select new
                {
                    id = student.StudentID,
                    rtcid = student.RTCStudentID,
                    firstname = student.FirstName,
                    lastname = student.LastName,
                    phone = student.Phone,
                    studentemail = student.StudentEmail,
                    personalemail = student.PersonalEmail,
                    address = student.Address,
                    city = student.City,
                    state = student.State,
                    zip = student.Zip,
                    birthdate = student.BirthDate,
                    gender = student.Gender,
                    notes = student.Notes,
                    studentdocumentslocation = student.StudentDocumentsLocation
                };