SELECT id as fil_id,name,search_url as fil_val,'courses'as fil_name
FROM courses
WHERE id IN (
SELECTTRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(branch_ids, ',', numbers.n), ',', -1)) AS branch_value
FROM institute_courses
JOIN institutes ON institutes.id = institute_courses.institute_id
INNERJOIN (
SELECT1AS n UNIONALLSELECT2UNIONALLSELECT3UNIONALLSELECT4UNIONALLSELECT5UNIONALLSELECT6UNIONALLSELECT7UNIONALLSELECT8UNIONALLSELECT9UNIONALLSELECT10
) AS numbers ONCHAR_LENGTH(branch_ids) -CHAR_LENGTH(REPLACE(branch_ids, ',', '')) >= numbers.n -1WHERE institute_courses.branch_ids !=''AND institute_courses.status ='active'AND institute_courses.course_type =33AND institutes.status ='Active'AND institutes.state =72
)
AND status ='Y'AND count_inst >20AND course_type =33AND search_url !=''ORDERBY fil_val ASC
SELECT id as fil_id, display_name as name, short_name as fil_val, 'exam'as fil_name
FROM criteria
WHERE id IN (
SELECTTRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(criteria, ',', numbers.n), ',', -1)) AS criteria_value
FROM institute_courses
JOIN institutes ON institutes.id = institute_courses.institute_id
INNERJOIN (
SELECT1AS n UNIONALLSELECT2UNIONALLSELECT3UNIONALLSELECT4UNIONALLSELECT5UNIONALLSELECT6UNIONALLSELECT7UNIONALLSELECT8UNIONALLSELECT9UNIONALLSELECT10
) AS numbers ONCHAR_LENGTH(criteria) -CHAR_LENGTH(REPLACE(criteria, ',', '')) >= numbers.n -1WHERE institute_courses.criteria !=''AND institute_courses.status ='active'AND institute_courses.course_type =33AND institutes.status ='Active'AND institutes.state =72
)
AND status ='Y'AND count_inst >20AND course_type =33ORDERBY short_name ASC
selectcount(*) as aggregate from (selectDISTINCT(institutes.id) AS inst_id, institutes.id , institutes.ics_link_2,institutes.is_cu_type,institutes.institute_name,
institutes.acreditation,institutes.ugc,institutes.institute_name_2,institutes.logo,institutes.data_source_image,'N'AS apply_status,institutes.aff_univ_id,
old_univ_id ,institutes.is_auto ,institutes.is_deemed,
institutes.cons_univ,institutes.etc,institutes.form_price,institutes.apply_link,institutes.univ_type,institutes.inst_state,institutes.inst_city,institutes.review_count,CAST( institutes.review_val ASDECIMAL(2, 1)
) AS review_val,
institute_courses.id as icid ,institute_courses.institute_id as institute_id , institute_courses.is_apply_now ,institute_courses.response_time_quater ,institute_courses.sr_rating ,institute_courses.duration ,institute_courses.duration_month ,institute_courses.duration_year ,institute_courses.total_fees_sorting ,institute_courses.criteria ,institute_courses.mint_rank_2017 AS mint_rank_2015 ,institute_courses.outlook_rank_2017 AS outlook_rank_2015 ,institute_courses.itoday_rank_2017 AS itoday_rank_2015 ,institute_courses.nirf_rank_2019 ,institute_courses.nirf_rank_2018 ,institute_courses.nirf_rank_2020 ,institute_courses.course_specific_name ,
(selectcount(id) from images where images.institute_id = institutes.id ) as img_count,
(select GROUP_CONCAT(images.name) from images where images.institute_id = institutes.id ) as img_name,
(selectcount(id) from document where document.institute_id = institutes.id ) as document_count,document.name,document.first_page,document.title,document.doc_type ,
course_type.course_name as sub_course_name , sub_course.id as sub_course_id , sub_course.sub_course_url as sub_course_url,
(SELECT h_package FROM placement_course WHERE placement_course.institute_course_id = institute_courses.id orderby year_placement desc limit 1) AS highest_package
from `institutes` innerjoin `institute_courses` on `institutes`.`id` = `institute_courses`.`institute_id` leftjoin `course_type` on `course_type`.`id` = `institute_courses`.`course_type` leftjoin `document` on `institutes`.`id` = `document`.`institute_id` leftjoin `sub_course` on `sub_course`.`id` = `institute_courses`.`sub_course_id` and `sub_course`.`sub_course_url` notlike'%(%'and `sub_course`.`sub_course_url` !=''leftjoin `placement_course` on `placement_course`.`institute_course_id` = `institute_courses`.`id` where `institutes`.`status` ='Active'and (`institutes`.`state` in (72) or `institutes`.`state2` in (72)) and `institute_courses`.`course_type` in (33) and `institute_courses`.`sequence` in (selectMIN(SEQUENCE) from `institute_courses` where `institute_id` = institutes.id and `institute_courses`.`course_type` in (33)) groupby `institutes`.`id`) as `aggregate_table`
selectDISTINCT(institutes.id) AS inst_id, institutes.id , institutes.ics_link_2,institutes.is_cu_type,institutes.institute_name,
institutes.acreditation,institutes.ugc,institutes.institute_name_2,institutes.logo,institutes.data_source_image,'N'AS apply_status,institutes.aff_univ_id,
old_univ_id ,institutes.is_auto ,institutes.is_deemed,
institutes.cons_univ,institutes.etc,institutes.form_price,institutes.apply_link,institutes.univ_type,institutes.inst_state,institutes.inst_city,institutes.review_count,CAST( institutes.review_val ASDECIMAL(2, 1)
) AS review_val,
institute_courses.id as icid ,institute_courses.institute_id as institute_id , institute_courses.is_apply_now ,institute_courses.response_time_quater ,institute_courses.sr_rating ,institute_courses.duration ,institute_courses.duration_month ,institute_courses.duration_year ,institute_courses.total_fees_sorting ,institute_courses.criteria ,institute_courses.mint_rank_2017 AS mint_rank_2015 ,institute_courses.outlook_rank_2017 AS outlook_rank_2015 ,institute_courses.itoday_rank_2017 AS itoday_rank_2015 ,institute_courses.nirf_rank_2019 ,institute_courses.nirf_rank_2018 ,institute_courses.nirf_rank_2020 ,institute_courses.course_specific_name ,
(selectcount(id) from images where images.institute_id = institutes.id ) as img_count,
(select GROUP_CONCAT(images.name) from images where images.institute_id = institutes.id ) as img_name,
(selectcount(id) from document where document.institute_id = institutes.id ) as document_count,document.name,document.first_page,document.title,document.doc_type ,
course_type.course_name as sub_course_name , sub_course.id as sub_course_id , sub_course.sub_course_url as sub_course_url,
(SELECT h_package FROM placement_course WHERE placement_course.institute_course_id = institute_courses.id orderby year_placement desc limit 1) AS highest_package
from `institutes` innerjoin `institute_courses` on `institutes`.`id` = `institute_courses`.`institute_id` leftjoin `course_type` on `course_type`.`id` = `institute_courses`.`course_type` leftjoin `document` on `institutes`.`id` = `document`.`institute_id` leftjoin `sub_course` on `sub_course`.`id` = `institute_courses`.`sub_course_id` and `sub_course`.`sub_course_url` notlike'%(%'and `sub_course`.`sub_course_url` !=''leftjoin `placement_course` on `placement_course`.`institute_course_id` = `institute_courses`.`id` where `institutes`.`status` ='Active'and (`institutes`.`state` in (72) or `institutes`.`state2` in (72)) and `institute_courses`.`course_type` in (33) and `institute_courses`.`sequence` in (selectMIN(SEQUENCE) from `institute_courses` where `institute_id` = institutes.id and `institute_courses`.`course_type` in (33)) groupby `institutes`.`id` orderby `institute_courses`.`sr_rating` desc, `institutes`.`clicks` desc limit 20offset0
select `PageSno`, `Header_Tag` as `header`, `sr_description`, `PageTitle` as `title`, `PageDescription` as `description`, `PageKeywords` as `keywords`, `title_as_heading` from `metatags` where `PageID` ='design-ug/colleges-delhi-ncr' limit 1