Find students studying two subjects with three different roles SQL

user_14969546 0
Viewed 155

I have a table students with student id, subjects and number of roles per subject. How to find students who studied at least two subjects where they had exactly three roles?

student    subject    num_roles
1000       223        2
1000       223        1
-------------------------------
1043       243        3
-------------------------------
1002       109        3
1002       230        3
1002       200        1            valid student
...

I can only think of finding students and their subjects that are equal to 3 num_roles. But how to modify the code to find students with 3 roles in at least 2 subjects?

SELECT s.student, s.subject
FROM students s
WHERE s.num_roles = 3 
ORDER BY s.student, s.subject;

Expected output:

student     subject 
1002        109
1002        230
...
postgresql sql
3 Answers
  Select student , subject 
  From students 
  where student in (
    Select student 
    From students 
    where num_roles = 3
    Group by student having count(subject) >1
  )
  and num_roles = 3
user_23108050

Simple and fast with EXISTS:

SELECT student, subject
FROM   students s1
WHERE  num_roles = 3
AND    EXISTS (  -- same student has another subject with 3 roles
   SELECT FROM students s2
   WHERE  s2.num_roles = 3
   AND    s2.student = s1.student
   AND    s2.subject <> s1.subject
   )
ORDER  BY 1,2;

A multicolumn index on (num_roles, student) would be the optimum for this query. (Typically, it won't pay to append the subject to the index.)

A query using GROUP BY & count() is more expensive and has to use a sequential scan of the whole table, plus another index (or sequential) scan. Test with EXPLAIN / EXPLAIN ANALYZE.

user_9398600

If you are satisfied with one row per student, you can use array_agg() to combine the subjects:

SELECT s.student, ARRAY_AGG(s.subject) as subjects
FROM students s
WHERE s.num_roles = 3 
ORDER BY s.student, s.subject
HAVING COUNT(*) >= 2;
user_11440350
Related