SQL Query to select subjects that satisfy prerequisite requirements

43
February 11, 2019, at 10:20 AM

I have the following tables:

COURSE

+----------+-------------------------+
| course_id |      course_name       |
+-----------+------------------------+
|        1  |                   s001 |
|        2  |                   s002 |
|        3  |                   s003 |
|        4  |                   s004 |
+-----------+------------------------+

COURSE_PREREQUISITES

+----------+-------------------------+
| course_id | prerequisite_course_id |
+-----------+------------------------+
|        3  |                   2    |
+-----------+------------------------+
|        4  |                   1    |
+-----------+------------------------+  
|        4  |                   2    |
+-----------+------------------------+
|        4  |                   3    |
+-----------+------------------------+

My question is: Given a list of Course IDs a student has completed, how can I obtain a list of courses the student is eligible to participate in?

Example

If a student has completed course_id 2, the query should return courses: 1, (since it has no prerequisites) and 3 but not 4 since 4 has 1,3 as prerequisites as well.

Attempt at a solution

I have tried using the IN statement like so for a student who has completed the course 2:

SELECT DISTINCT course_id FROM COURSE_PREREQUISITES
  WHERE prerequisite_course_id IN (2) 

but it obviously fails since it returns all courses that satisfy at least one prerequisite which is not what I need.

I came across this question which is similar: Select rows that match all items in a list. But the provided solution does not work in my case, since the number of prerequisites for a course is not fixed.

Finally, I would also like to know if NOSQL databases (couchDB, mongoDB) are better suited for problems like these.

Answer 1

In MySQL you can use FIND_IN_SET to get the results you want using this query, which compares the number of courses completed with the number of pre-requisites of each course. The results includes courses which have no pre-requisites (where the student has not already completed that course).

SET @courses_completed = '2';
SELECT c.course_id
FROM course c
LEFT JOIN course_prerequisites p ON p.course_id = c.course_id
WHERE NOT FIND_IN_SET(c.course_id, @courses_completed)
GROUP BY c.course_id
HAVING SUM(COALESCE(FIND_IN_SET(p.prerequisite_course_id, @courses_completed), 0) > 0) = COUNT(p.prerequisite_course_id);

Output:

course_id
1
3

I've made a demo on SQLFiddle with various values of @courses_completed to show possible variants of courses the student is eligible for.

Answer 2
accept cid;
select a.course_id from 
(select course_id, max(prerequisite_course_id) as prerequisite_course_id from course_prerequisites 
group by course_id 
having count(*)=1) a 
where a.prerequisite_course_id=&cid
union
select b.course_id from
(select course_id from course where course_id!=&cid) b
left join course_prerequisites c 
on b.course_id=c.course_id where c.course_id is null;

The first half before the union is to get the course_id for the course which has the supplied input as prerequisite and the other half after the union is to select the courses that don't have any prerequisites.

This works in oracle. The accept is to get input at run time. For the other dbs you can ignore the accept statement and pass in the course_id in place of &cid.

Answer 3

With a left join from COURSE to COURSE_PREREQUISITES:

select c.*
from course c left join course_prerequisites cp
on cp.course_id = c.course_id
where 
  c.course_id <> 2
  and 
  (
    cp.prerequisite_course_id is null
    or
    (
      cp.prerequisite_course_id = 2
      and
      (select count(*) from course_prerequisites where course_id = c.course_id) = 1
    )
  ) 
order by c.course_id

See the demo

Answer 4

Assuming you have for example two input courses (1,2) then you may use the following query

select distinct c.course_id 
from courses c
left join course_prerequisites cp on cp.course_id = c.course_id
group by c.course_id
having count(case when cp.prerequisite_course_id not in (1,2) then 1 end) = 0
Answer 5

Assuming you only care about courses with pre-requisites, this should get what you want:

select cp.course_id
from course_prerequisites cp
group by cp.course_id
having count(*) = sum( prerequisite_course_id in ( <list of taken courses goes here> ) );

The sum() is counting the number of courses that match the pre-requisites for a given course. The count(). The = count(*) is requiring that this match the courses the student has taken.

Then, there are the courses without pre-requisites. So:

(select cp.course_id
 from course_prerequisites cp
 group by cp.course_id
 having count(*) = sum( prerequisite_course_id in ( <list of taken courses goes here> ) )
) union all
(select c.course_id
 from courses c
 where not exists (select 1
                   from course_prerequisites cp
                   where cp.course_id = c.course_id
                  )
);

You can actually do this without the union all . . .:

select c.course_id
from courses c left join
     course_prerequisites cp
     on c.course_id = cp.course_id
group by c.course_id
having count(cp.course_id) = sum( cp.prerequisite_course_id in ( <list of taken courses goes here> ) );

The logic here is the same as in the first query, except that courses with no pre-requisites are included and count(cp.course_id) can be 0.

READ ALSO
mysql database check if domain name exists / alive [duplicate]

mysql database check if domain name exists / alive [duplicate]

This question already has an answer here:

27
My mysql stored procedure row_count() inside another stored Procedure always returns zero

My mysql stored procedure row_count() inside another stored Procedure always returns zero

Mysql ROW_COUNT() returns zero even if the update changes the databaseI have a Stored Procedure A which call's another Stored Procedure B which has a Update Statement

28
FreeRADIUS 3 stores # in groupname as =23 for %{SQL-Group}

FreeRADIUS 3 stores # in groupname as =23 for %{SQL-Group}

i have a user bipin linked to a usergroup called #dl#-daily-plan which FR is able to read from mysql DB but it stores it as =23dl=23-daily-plan in %{SQL-Group}Is there any specific reasoning for this as im on the vanilla config of FR and it seems to happen...

54
How to send GPS location to mysql table every x minutes

How to send GPS location to mysql table every x minutes

I'm working on a bit of code to save the user's current GPS coordinates to a mysql table every 5 minutesI'm familiar with PHP and learning javascript at the moment so I need some assistance with the javascript portion of the code

39