Match two distinct values from a single column in a joined table

Posted on

Question :

I have three tables:

class:
class_id
class_name

student:
student_id
student_name

class_schedule:
class_id
student_id

I want to select all the classes where studentA and studentB are in the same class using the student names. I can use a subquery to pull all the classes studentA is in, and then from that subset the classes that studentB is in and that works. That being said, it is terribly inefficient. I have tried a number of solutions including joining the same table twice, once for each value I want to find, but always get an empty result set.

For testing and prototyping purposes I am using sqlite, but will reside on DB2 long term.

Answer :

Select c.class_id, c.class_name
  From class c, class_schedule cs
  Where c.class_id = cs.class_id
    And cs.student_id in (
          select student_id from student
            where student_name in ('studentA','studentB'))
  Group by c.class_id, c.class_name
  Having count(*) = 2

Check it out: DB Fiddle

Leave a Reply

Your email address will not be published. Required fields are marked *