Post reply

Common records

  • January 31, 2003 at 10:37 pm

    #80197

    I have a table called class_students which contins the student id's for the particular class, for eg. class (id-1) can cantain 2 students (ids 1 and 2). another class (id-2) which contains students (ids 1 ,2 and 3).

    I want to get only those students which are common to classes 1 and 2.

  • February 3, 2003 at 8:00 am

    This was removed by the editor as SPAM

  • February 3, 2003 at 9:26 am

    #447833

    If this is a specific problem, only relating to the class id-1 and class id-2, the following query will do the trick.

    

    SELECT cs1.student_id

    FROM (SELECT student_id 

          FROM class_students

          WHERE class_id = 1) cs1

       INNER JOIN

         (SELECT student_id 

          FROM class_students

          WHERE class_id = 2) cs2

       ON cs1.student_id = cs2.student_id

    A more general solution, that gives you an overview of all students that are enrolled for more than one class would be :

    

    SELECT student_id, COUNT(*) as NrOfClasses

    FROM class_students

    GROUP BY student_id

    HAVING COUNT(*) > 1

  • February 6, 2003 at 10:57 pm

    #448331

    Thanx peeters, it did worked

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply