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

join;sqlite

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.

Best 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

Related Solutions

Mysql – Group By on column from joined table

Probably because the table "dict_verbs_german" is the last table joined.

When I last researched this, "using temporary; using filesort" meant MySQL was writing the the join result into a temporary table, then sorting. (See note below.) In order to avoid "using temporary; using filesort", you'd have to meet all these conditions.

dict_verbs_german is the first non-constant table in the JOIN order, and
dict_verbs_german has an index that marches the ORDER BY column list.

To avoid "using temporary", you'd have to meet all these conditions.

dict_verbs_german is the first table in the JOIN order, and
dict_verbs_german has an index that marches the ORDER BY column list.

Note: I'm talking about ORDER BY, because that's specifically what my research dealt with, and I'm pulling these notes from an email I wrote in 2010. I think the same optimizations applied to GROUP BY, but I could be wrong. Improvements to the optimizer over the last couple of years might have changed things, too.

MySQL documentation

In some cases, MySQL cannot use indexes to resolve the ORDER BY . . .. These cases include . . . joining many tables, and the columns in the ORDER BY are not all from the first nonconstant table that is used to retrieve rows. (This is the first table in the EXPLAIN output that does not have a const join type.)

MySQL documentation for optimizing limit queries

(I don't think this last reference will help you with this problem, but it's good to know where to find it.)

Later . . .

Cutting the WHERE clause seems to eliminate the filesort. (That might not be true for real-world data.) Maybe you can create a view without a WHERE clause and a LIMIT clause, then query the view and still avoid a filesort. I'm sorry that, for personal reasons, I'm not able to test that for you right now.

MySQL Many-to-Many Join – How to Perform Many-to-Many Join with Left Join

As a rule of thumb conditions on the Outer table are usually placed in WHERE, but on the Inner table in ON:

SELECT  DISTINCT student.Student_ID as 'Student_ID',
                  case when class.class_id is null then 'No Class ATM' else class.class_id end as 'Class_ID',
                  case when Attendance.Attendance_Status is null then 'Not Present' else Attendance.Attendance_Status end as 'attendence_status'
          FROM Student
          LEFT JOIN Student_Has_Class ON(student_has_class.student_id = student.student_id)
          LEFT JOIN class ON class.class_id = student_has_class.class_id
            AND NOW() between class.class_start_timestamp and class.class_end_timestamp
          LEFT JOIN Subject ON(class.subject_id = subject.subject_id)
          LEFT JOIN Attendance on(Attendance.class_id = class.class_ID
                                  AND Attendance.student_id = student.student_id)
          WHERE Student.student_id like '1'

And please no like '1', it's = '1', but probably student_id is numeric so = 1

Best Answer

Related Solutions

Mysql – Group By on column from joined table

MySQL Many-to-Many Join – How to Perform Many-to-Many Join with Left Join

Related Question