Search code examples
mysqlrelational-division

I want to display the student id, who having all the above 5 units?

Could u please help me on this my sql query,

select STUD_ID,unit_c 
from FCHE_grad 
where (unit_c in("C0001","C0002","ENG300","K0001","K0002")) 
order by STUD_ID

the above query returns student_id, who have 4 unit . I want to display the student id, who having all the above 5 units?

Solution

  • Use a having cluase to check if the student is in all 5 units

    select * from FCHE_grad
where stud_id in (select STUD_ID
from FCHE_grad 
where (unit_c in('C0001','C0002','ENG300','K0001','K0002')) 
group by STUD_ID
having count(stud_id)=5);

    Fiddle