CODEWITHSUNDEEP
mysqlsql
Steve D
Steve D

Reputation: 393

Get every row in a table if (as a group) they satisfy a condition

I have two tables: Students (id, fname, lname) and Registrar (id, student_id, class_id). I teach a class with class ID 99. I want to find all students in my class, and list those students and all the other classes they're taking. I can do the following:

SELECT s.fname, s.lname, r.class_id FROM
    Students AS s JOIN Registrar AS r ON s.id = r.student_id WHERE
      r.student_id IN
         (SELECT student_id FROM Registrar WHERE class_id = 99);

This works, but it's slow. I don't have a lot of experience with this (and as my title suggests, I have no idea what this is called), but I'm guessing it's a fairly common problem to solve. Any pointers in the right direction are most welcome.

Upvotes: 1

Views: 86

Answers (3)

Rogala
Rogala

Reputation: 2773

You can also join with multiple items in your clause:

SELECT s.fname, s.lname, r.class_id
FROM Registrar r
    INNER JOIN 
        (SELECT student_id 
        FROM Registrar 
        WHERE class_id = 99) as t
    ON r.student_id = t.student_id
    INNER JOIN Students s
    ON r.student_id = s.id

You really want to try to get as much done in your joins as you can.

Upvotes: 1

pala_
pala_

Reputation: 9010

This should work. Join to the registar table once on the class_id = 99 condition, and then again with no class_id restriction to get ALL the classes for the student.

select s.fname, s.lname, r2.class_id
  from students s
    inner join registrar r
      on s.id = r.student_id
        and r.class_id = 99
    inner join registrar r2
      on s.id = r2.student_id;

demo fiddle here

With an index (student_id, class_id) on registrar table, and on id in student table, this should be quite fast.

based on comments it's possible classes may be repeated - we can just group to get rid of those:

select s.fname, s.lname, r2.class_id
  from students s
    inner join registrar r
      on s.id = r.student_id
        and r.class_id = 99
    inner join registrar r2
      on s.id = r2.student_id
  group by s.id, r2.class_id

This abuses mysql's handling of group by somewhat, but its okay because fname and lname are identical in every row within each group

Upvotes: 4

turutosiya
turutosiya

Reputation: 1041

IN statement is usually slow. Then you can get better performance by using EXISTS statement like :

SELECT s.fname, 
       s.lname, 
       r.class_id 
FROM   students AS s 
       JOIN registrar AS r 
         ON s.id = r.student_id 
WHERE  EXISTS (SELECT 1 
               FROM   registrar 
               WHERE  registrar.student_id = s.id 
                      AND registrar.class_id = 99);

Upvotes: 2

Related Questions