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.
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