Help with query

[andretru2]

Hi,

I have two tables:
Courses and Exams.

Courses contain the courses students took.
Exams contain the exams students took for a particular course.

For instance:

course:
StudentID CourseNumber
2 2133
2 2839
2 3879
10 2133
10 3879
22 3879
22 2133
10 2839

exams:
StudentID CourseNumber
2 2133
2 2839
2 3879
10 2133
10 3879

I need to know which students took exams for ALL courses taken. In this case, the result would be studentid = 2
I also need to know which students took exams for SOME courses taken. In this case, the result would be studentid=10

I need the equivalent SQL statement for each query.
Any help is appreciated.

Thanks

[alansidman]

Will this solution work for you? I created a series of queries to get this result.
Query 1:
SELECT Courses.SudentID, Count(Courses.CourseNumber) AS CountOfCourseNumber
FROM Courses
GROUP BY Courses.SudentID;

Query2:
SELECT Exams.StudentID, Count(Exams.CourseNumber) AS CountOfCourseNumber
FROM Exams
GROUP BY Exams.StudentID;

Query3:
SELECT Query1.SudentID, Query1.CountOfCourseNumber AS [Courses Taken], Query2.CountOfCourseNumber AS [Exams Taken]
FROM Query1 LEFT JOIN Query2 ON Query1.SudentID = Query2.StudentID;

[andretru2]

Thank you but the solution is not optimal because there are students that took exams for courses they did not take - thus should not be counted in the query.
In addition, the db is quite large and comparing the counts will not work efficiently.

Any other suggestions are appreciated.

[alansidman]

I have added two more queries to get to the end result.

Query4
SELECT Query2.StudentID, Query1.CountOfCourseNumber AS [Courses Taken], Query2.CountOfCourseNumber AS [Exams Taken]
FROM Query1 RIGHT JOIN Query2 ON Query1.SudentID = Query2.StudentID;


Query5
SELECT Query1.SudentID, Query1.CountOfCourseNumber AS [Courses Taken], Query2.CountOfCourseNumber AS [Exams Taken]
FROM Query1 LEFT JOIN Query2 ON Query1.SudentID = Query2.StudentID;

UNION

SELECT Query2.StudentID, Query1.CountOfCourseNumber AS [Courses Taken], Query2.CountOfCourseNumber AS [Exams Taken]
FROM Query1 RIGHT JOIN Query2 ON Query1.SudentID = Query2.StudentID;