I have category structure as given below.
Country
country1,country2, country3
Basic ,Classic, Premium (subscription models)
Primary, Secondary
standard 1 to standard 8
subject1 to subject7
three courses in each subject
6 exams are there in each course
From country till subject are categories stored in course_categories table in moodle, where country is the parent category of country1,country2, country3 and under each country we have Basic, Classic, Premium as sub categories and so on . We want to list all the users who are taking each exam. To know which course the exam is and in turn to know which subject the course is present and looping back to know its parent.
Please find the attachment for the inner join we have added and please let us know how to optimize that