1

I am developing a simple course suggestion application. which suggest a degree course to students based on their diploma result.

here are my tables in my database.

+-----------------------------+
| Tables_in_available_courses |
+-----------------------------+
| course                      |
| course_requirements         |
| qualification               |
| result                      |
| student                     |
| student_result              |
+-----------------------------+

A student have multiple qualifications and a course also have multiple qualifications requirements.

This system should print all suggested courses to every student in the database according to their result.

eg: my results are Diploma in computing - A Diploma in management - B Diploma in software engineering - C

and Computer science degree course's pre-requests are Diploma in computing - A Diploma in software engineering - C

So the system should suggest to me Computer Science

    mysql> show columns from student_result;
+------------------+---------+------+-----+---------+----------------+
| Field            | Type    | Null | Key | Default | Extra          |
+------------------+---------+------+-----+---------+----------------+
| id               | int(11) | NO   | PRI | NULL    | auto_increment |
| qualification_id | int(11) | YES  | MUL | NULL    |                |
| result_id        | int(11) | YES  | MUL | NULL    |                |
| student_id       | int(11) | YES  | MUL | NULL    |                |
+------------------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> show columns from course_requirements;
+------------------+---------+------+-----+---------+----------------+
| Field            | Type    | Null | Key | Default | Extra          |
+------------------+---------+------+-----+---------+----------------+
| id               | int(11) | NO   | PRI | NULL    | auto_increment |
| course_id        | int(11) | NO   | MUL | NULL    |                |
| qualification_id | int(11) | YES  | MUL | NULL    |                |
| result_id        | int(11) | YES  | MUL | NULL    |                |
+------------------+---------+------+-----+---------+----------------+

How to implement it?

0
WITH cte1 AS ( SELECT cr.course_id, sr.student_id, COUNT(*) cnt
               FROM course_requirements cr
               JOIN student_result sr ON cr.qualification_id = sr.qualification_id
               GROUP BY cr.course_id, sr.student_id
             ), 
     cte2 AS ( SELECT course_id, COUNT(qualification_id) cnt
               FROM course_requirements
               GROUP BY course_id
             )
SELECT DISTINCT cte1.course_id, cte1.student_id
FROM cte1, cte2
WHERE cte1.cnt = cte2.cnt
ORDER BY 1,2

fiddle

Add proper conditions by result_id fields values if needed.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.