Find ALL the prerequisites for a given course

I have the following tables:

COURSE_PREREQUISITES

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

My question is: How can I obtain all the course ids a student needs to take before he can take course id 6?

Intended Answer: I should obtain the course id of 1, 2, 3, 4, 5.

Attempts Made: I have tried selecting the prerequisite_course_id of the intended final course id 6, and nesting it within other select statements in order to obtain the full list of prerequisite_course_id, but I am stuck at implementing it, and I do not think this is a viable solution as well.

SELECT prerequisite_course_id FROM course_prerequisites WHERE course_id = 6

Answer

You want a recursive CTE:

with recursive cte as (
      select prerequisite_course_id
      from course_prerequisites
      where course_id = 6
      union all
      select cp.prerequisite_course_id
      from cte join
           course_prerequisites cp
           on cte.prerequisite_course_id = cp.course_id
    )
select *
from cte;

Here is a db<>fiddle.