Search code examples
mysqlsqlrelational-division

Find the id of students who take every course, in mysql

I have 3 tables:

Student (Id, Name, Country) 
Course (CrsCode, CrsName, Type, Instructor) 
Results(Id, CrsCode, Grade)

I have to solve below q's by using SQL query. Id and CrsCode are key fields in every table. The Type field specifies the course type, e.g. MATH, STAT, SYSC, TTMG, ELEC, etc.

  1. Find the Id of students who take TTMG or SYSC course.
  2. Find the Id of students who take every course.
  3. Find the id of students who take every TTMG course or every SYSC course.

Below are the sample data for part 3. 1st image is the Course Table and 2nd image is the Results table

enter image description here

enter image description here

I am able to solve the 1st question by using the following SQL query:

SELECT R.ID FROM RESULTS R JOIN COURSE C ON C.CRSCODE = R.CRSCODE WHERE C.TYPE="TTMG" OR C.TYPE ='SYSC

For the 2nd question, I believe we have to again relate 2nd (Course) and 3rd (Results) table in order to get the result. We have to relate a specific case here. We have to consider a case that there is one specific student who is taking all courses(CrsCode) and we have to find the id of that student.

I believe the query will still be the same as in question 1 but this time there will be little bit changes:

SELECT R.ID FROM RESULTS R JOIN COURSE C

I am not including anything after COURSE C because I am not sure the answer after that. Any pointers will be highly appreciated.

Solution

  • Find the Id of students who take every course.

    Assuming course table contains all the courses a student can take, you can group by the id column in the results table and check if the count is equal to the row count in course table. 

    SELECT ID
FROM RESULTS
GROUP BY ID
HAVING COUNT(DISTINCT CRSCODE) = (SELECT COUNT(*) FROM COURSE)

    Edit: Based on OP's comment

    Find the id of students who take every TTMG or SYSC course

    SELECT r.id
FROM  course c
JOIN RESULTS r on c.CRSCODE=r.CRSCODE
GROUP BY r.ID 
HAVING COUNT(case when type = 'SYSC' then r.CRSCODE end)  = (SELECT COUNT(*) FROM COURSE WHERE type = 'SYSC')
OR COUNT(case when type = 'TTMG' then r.CRSCODE end) = (SELECT COUNT(*) FROM COURSE WHERE type = 'TTMG')

    Sample Demo