Complex Search results query MySQL

search.sql

SELECT
    edu.*,
    Educator.*,
    cCat.`courseObjectiveName`,
    cGoal.`courseGoalName`,
    cTop.`courseExamName`,
    cCon.`courseSubjectName`
FROM ".EducatorCoursesModel::$coursesTableName." AS edu
LEFT JOIN " . courseObjectiveModel::$tableName . " AS cCat
ON edu.`courseObjectiveId`=cCat.`courseObjectiveId`
LEFT JOIN ".CourseGoalsModel::$tableName." AS cGoal
ON cGoal.`courseGoalId`=edu.`courseGoalId`
LEFT JOIN " . courseExamModel::$tableName . " AS cTop
ON find_in_set(cTop.`courseExamId`,edu.`courseExamId`)
LEFT JOIN " . courseSubjectModel::$tableName . " AS cCon
ON find_in_set(cCon.`courseSubjectId`,edu.`courseSubjectId`)
LEFT JOIN " . EducatorsModel::$tableName . " AS Educator
ON find_in_set(Educator.`educatorId`,edu.`educatorId`)
WHERE CONCAT(
    edu.courseName,
    edu.courseTags,
    edu.courseTargetAudience,
    edu.courseDescription,
    cCat.courseObjectiveName,
    cGoal.courseGoalName,
    cTop.courseExamName,
    cCon.courseSubjectName,
    Educator.name             
)
REGEXP '[[:<:]]".$keyWord."[[:>:]]';

---------------------------------------------------------------------------------------


SELECT
    edu.*,
    Educator.*,
    cCat.`courseObjectiveName`,
    cGoal.`courseGoalName`,
    cTop.`courseExamName`,
    cCon.`courseSubjectName`
FROM educatorCourses AS edu
LEFT JOIN courseObjective AS cCat
ON edu.`courseObjectiveId`=cCat.`courseObjectiveId`
LEFT JOIN courseGoals AS cGoal
ON cGoal.`courseGoalId`=edu.`courseGoalId`
LEFT JOIN courseExam AS cTop
ON find_in_set(cTop.`courseExamId`,edu.`courseExamId`)
LEFT JOIN courseSubject AS cCon
ON find_in_set(cCon.`courseSubjectId`,edu.`courseSubjectId`)
LEFT JOIN educators AS Educator
ON find_in_set(Educator.`educatorId`,edu.`educatorId`)
WHERE 
    LOCATE('ssc', edu.courseName)  OR  
    LOCATE('ssc', edu.courseTags)  OR  
    LOCATE('ssc', edu.courseTargetAudience)  OR  
    LOCATE('ssc', edu.courseDescription)  OR  
    LOCATE('ssc', cCat.courseObjectiveName)  OR  
    LOCATE('ssc', cGoal.courseGoalName)  OR  
    LOCATE('ssc', cTop.courseExamName)  OR  
    LOCATE('ssc', cCon.courseSubjectName)  OR  
    LOCATE('ssc', Educator.name)  OR  
    LOCATE('cgl', edu.courseName)  OR  
    LOCATE('cgl', edu.courseTags)  OR  
    LOCATE('cgl', edu.courseTargetAudience)  OR  
    LOCATE('cgl', edu.courseDescription)  OR  
    LOCATE('cgl', cCat.courseObjectiveName)  OR  
    LOCATE('cgl', cGoal.courseGoalName)  OR  
    LOCATE('cgl', cTop.courseExamName)  OR  
    LOCATE('cgl', cCon.courseSubjectName)  OR  
    LOCATE('cgl', Educator.name);

-------------------------------------------------------------------------