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.