(SELECT M1.mtype,C1.profile_id,M1.memtype,M1.compurl as compurl,C1.compaddr,C1.phone_number,C1.fax_no,C1.business_type as btype,C1.company_info as companyinfo,C1.company_video_urls as video ,C1.company_logo AS companylogo,C1.profile_page as profile_page ,C1.city AS city,M1.company_name as companyname,M1.country_code as country,(SELECT short_name FROM country WHERE country_code=M1.country_code) As country_name,M1.`mid` , P1.product_id,1 as priority FROM mreg M1 INNER JOIN product P1 ON M1.mid = P1.mid INNER JOIN com_prof C1 ON P1.mid=C1.mid where P1.status=1 and M1.ignore_status=0 and C1.visibility_status in (1,5) GROUP BY M1.`mid` ) UNION (SELECT M1.mtype,C1.profile_id,M1.memtype,M1.compurl as compurl,C1.compaddr,C1.phone_number,C1.fax_no,C1.business_type as btype,C1.company_info as companyinfo,C1.company_video_urls as video ,C1.company_logo AS companylogo,C1.profile_page as profile_page ,C1.city AS city,M1.company_name as companyname,M1.country_code as country,(SELECT short_name FROM country WHERE country_code=country) As country_name,M1.`mid`,1 AS product_id,2 as priority FROM mreg M1 INNER JOIN com_prof C1 ON M1.mid=C1.mid where M1.ignore_status=0 AND C1.visibility_status in (1,5) AND C1.business_type REGEXP '7|3|12|1|10|13|2' AND NOT EXISTS(select mid from product where mid=M1.mid and status=1 ) GROUP BY M1.`mid` ) UNION (SELECT 1 AS mtype,directory_id as profile_id,businesstype As memtype,company_name AS compurl,address as compaddr,phonenumber as Phone_number,faxnumber as fax_no,businesstype as btype,description as companyinfo,companyvideo AS video,companylogo AS companylogo,1 AS profile_page,1 AS city,company_name AS companyname,countrycode AS country,(SELECT short_name FROM country WHERE country_code=country) As country_name,1 AS mid,1 AS product_id,3 as priority FROM com_prof2 WHERE emailid!='' AND status in(0,1) AND businesstype REGEXP '7|3|12|1|10|13|2' AND (description!='' or address!='') AND companylogo!='' AND description !='' ) ORDER BY mtype DESC, mid DESC