Results 1 to 2 of 2

Thread: Query Question

  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Unanswered: Query Question

    A quick setup of my scenario:

    I have three tables that contain information about students that have attended classes, the scheduled dates of those classes and the grouping of the classes into class sets. I would like to find out which of the students attended all three classes within a particular class set. The tables are arranged as follows:

    Attendance Table: UID = Student ID; Schedule ID = Reference to the schedule (The record in the attendance table signifies that the student has attended the class.)

    Schedule Table: ScheduleID = Unique Key; ClassID = Reference to the class; SetID = Reference to the Set that the Class belongs to.

    Sets Table: SetID = Unique Key; Class1ID = Class 1 Reference; Class2ID = Class 2 Reference; Class3ID = Class 3 Reference

    *****

    The logic to locate each UID that attended each of the three classes in the given set, through the @SetID param, has been evading me. I have attempted multiple types of Joins, and the best that I have come up with is a result of all people that have attended the classes.

    I thought I could nest multiple WHERE UID IN clauses together but I could not get them to work. (Incorrect Syntax...)

    Any suggestions or help would be greatly appreciated.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Query Question

    That's a poor table design. You would be better off with a Set_Classes table like this:

    create table sets( set_id int primary key, ... );
    create table set_classes
    ( set_id int references sets
    , class_id references classes
    , primary key( set_id, class_id) );

    (This is assuming a class can belong to more than one set, right? Otherwise you would just add set_id to the classes table.)

    Now, schedules no longer needs set_id, as that is available via set_classes:

    create table schedules
    ( schedule_id int primary key
    , class_id references classes
    );

    Now I think your query may be:

    select a.student_id
    from attendances a, schedules s, set_classes sc
    where a.schedule_id = s.schedule_id
    and s.class_id = sc.class_id
    and sc.set_id = @set_id
    group by a.student_id
    having count(distinct s.class_id) =
    ( select count(*) from set_classes where set_id=@set_id );

    or (there are always several ways to do same thing):

    select st.student_id from students st
    where not exists
    ( select class_id from set_classes where set_id=@set_id
    MINS
    select s.class_id from schedules s, attendances a
    where s.schedule_id = a.schedule_id
    and a.student_id = st.student_id
    );

    With your current table design it would have to be something like:

    select st.student_id
    from students st
    , attendances a1
    , attendances a2
    , attendances a3
    , schedules s1
    , schedules s2
    , schedules s3
    , sets s
    where s.set_id = @set_id
    and s.class1id = s1.class_id
    and s1.schedule_id = a1.schedule_id
    and a1.student_id = st.student_id
    and s.class1id = s2.class_id
    and s2.schedule_id = a2.schedule_id
    and a2.student_id = st.student_id
    and s.class1id = s3.class_id
    and s3.schedule_id = a3.schedule_id
    and a3.student_id = st.student_id;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •