SELECT DISTINCT a.id, a.company_name,a.seo_url,a.contactperson,a.tags,a.address,a.area,a.statecountry,
a.city,a.pincode,a.phone,a.mobile,a.email,a.website,area.name as area ,city.name as city,
a.shortdescription,a.smallbanner,a.profileaccount,a.bigbanner FROM profiles a
left JOIN main_category b ON b.id = a.d_mc_id and (b.name LIKE '%fair%' OR b.name LIKE '%and%' OR b.name LIKE '%hair%' OR b.name LIKE '%tirunelveli%')
left JOIN (select d.id from sub_category as d where (d.name LIKE '%fair%' OR d.name LIKE '%and%' OR d.name LIKE '%hair%' OR d.name LIKE '%tirunelveli%')) as d ON ( d.id =a.d_sc_id or a.subcat_id like CONCAT('%,',d.id,',%'))
INNER JOIN city ON city.id = a.city
INNER JOIN area ON area.id = a.area AND (REPLACE(area.name,' ','') Like'fair' OR REPLACE(area.name,' ','') Like'and' OR REPLACE(area.name,' ','') Like'hair' OR REPLACE(area.name,' ','') Like'tirunelveli') WHERE (a.city= '1' OR a.mulcity LIKE '%~1~%')
AND a.status = 1 AND a.profileaccount!=5 and (a.company_name LIKE '%fair%' OR a.company_name LIKE '%and%' OR a.company_name LIKE '%hair%' OR a.company_name LIKE '%tirunelveli%') AND (b.id is not null or d.id is not null) ORDER BY a.profileaccount DESC,a.id limit 0 , 20
