Thread: Help with query

    #1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    2
    Rep Power
    0

    Help with query


    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
  2. #2
  3. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Location
    Lake County, IL
    Posts
    865
    Rep Power
    152
    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;
    Alan Sidman
    Office 2010, 2007, 2002

    If I helped you, then click "give rep" button in the lower left corner.
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    2
    Rep Power
    0
    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.
  6. #4
  7. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Location
    Lake County, IL
    Posts
    865
    Rep Power
    152
    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;

Similar Threads

  1. Replies: 0
    Last Post: August 26th, 2008, 04:45 AM
  2. Replies: 14
    Last Post: February 7th, 2007, 07:31 AM
  3. Replies: 9
    Last Post: March 9th, 2005, 11:28 PM
  4. Replies: 1
    Last Post: October 20th, 2004, 03:50 AM
  5. MAke query and Select Query give diff results
    By AnalystDP in forum Microsoft Access Help
    Replies: 2
    Last Post: June 24th, 2004, 01:36 PM

IMN logo majestic logo threadwatch logo seochat tools logo