Notices

Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

Thread: Help with query

Results 1 to 4 of 4
Share This Thread →
  1. #1
    andretru2 is offline Registered User
    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
    alansidman's Avatar
    alansidman is offline Contributing User
    Join Date
    Feb 2008
    Location
    Lake County, IL
    Posts
    864
    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.

  3. #3
    andretru2 is offline Registered User
    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.

  4. #4
    alansidman's Avatar
    alansidman is offline Contributing User
    Join Date
    Feb 2008
    Location
    Lake County, IL
    Posts
    864
    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;

Share This Thread →

Become Part of This Conversation

Join NowFor Free!

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

ASP Free Advertisers and Affiliates