Find the value between two tables

Posted on

Question :

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?

Answer :

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.

Leave a Reply

Your email address will not be published. Required fields are marked *