父子关系的MySql查询优化

I am in trouble with large database with one of my query which is obvious not correct and I am not good in database side so I am not sure what I can do to optimize this query. Below is my table structure and details what data I need to fetch. I am in trouble with large database with one of