SELECT c.id as cid, c.title, c.course_description, c.programme_id, 
      c.department_id, c.code, c.unit, c.level, c.semester_id, pc.seen_as_elective as course_type, c.course_description, 
      pc.department_id AS pc_did, pc.programme_id AS pc_pid, pc.actual_programme_id, pc.course_id AS pc_cid, pc.seen_as_elective, 
      pc.session_id, pc.semester_id AS pc_semester, pc.level AS pc_level, pc.programme_type_id, pc.status 
      FROM courses c RIGHT JOIN programmes_courses pc 
      ON c.id = pc.course_id
      WHERE pc.semester_id = '1'
      AND pc.actual_programme_id = '9'
      AND pc.special_student = '0'
      AND pc.level = '300'
      AND pc.programme_type_id = '1'
      AND pc.status = 0
      GROUP BY c.id
      ORDER BY pc.seen_as_elective ASC, c.code ASC