Why is the following SQL Query returning an empty result?

I have the following table structures:

1 – Course(course_id, course_nam, language, course_price, create_date, average_rating, category, course_description, certifica_price, course_creator_id)

2 – Student(student_id, wallet)

3 – Discount(discount_id, discounted_course_id, allower_course_creator_id, is_allowed, start_date, end_date, percentage)

4 – Enrolls(student_id, course_id, purchased_price, purchase_date)

I want to write a query which will return every course of a course creator with id=$person_id along with the followings:

1 – If there is a discount on that course, and if the current date is between the start_date and end_date of that discount, the price of the course will be reduced according to the percentage.

2 – Number of students who has enrolled in that course. If there is no such student, 0.

3 – Course_id, course_name, average_rating, the discount percentage, and the price.

I have the following query

SELECT  C.course_id, C.course_name, C.average_rating, total_student, 
                            D.percentage, (CASE WHEN CURRENT_DATE >= D.start_date AND 
                            CURRENT_DATE < D.end_date AND D.is_allowed
                            THEN C.course_price * (( 100 - D.percentage ) / 100)
                            ELSE C.course_price END) as price
                    FROM        course C LEFT OUTER JOIN discount D ON 
                                C.course_id = D.discounted_course_id, 
                                (SELECT     course_id, COUNT(student_id) AS total_student
                                FROM        enrolls
                                WHERE   course_id IN (SELECT course_id
                                                    FROM course
                                                    WHERE course_creator_id = '$person_id')
                                                    GROUP BY    course_id) course_student
                    WHERE   course_student.course_id = C.course_id

When student, discount, enrolls tables are empty, the query does not return anything. I want it to return Course information with total_student = 0 (or null), percentage = 0 (or null, does not matter), price (original price of the course).

Answer

Try doing something like this

SELECT  
  C.course_id, C.course_name, C.average_rating, total_student, 
  D.percentage, (CASE WHEN CURRENT_DATE >= D.start_date AND 
  CURRENT_DATE < D.end_date AND D.is_allowed THEN C.course_price * (( 100 - D.percentage ) / 100)
    ELSE C.course_price END) as price
FROM course C 
LEFT OUTER JOIN discount D 
  ON  C.course_id = D.discounted_course_id
LEFT JOIN (
  SELECT course_id, COUNT(student_id) AS total_student
  FROM        enrolls
  WHERE   course_id IN (
    SELECT course_id
    FROM course
    WHERE course_creator_id = '$person_id')
  ON C.course_id = course_student.course_id
  GROUP BY    course_id) course_student