select distinct country_id ,country_code from ( SELECT distinct a.city_id ,a.city_name, a.city_name_th ,a.city_code ,b.region_id ,b.region_name, b.region_name_th ,c.country_id ,c.country_name,c.country_name_th ,c.country_code ,d.continent_code ,e.des_code,e.des_id,e.des_name,e.des_name_th from tbl_city a inner join tbl_region b on a.FK_region_id = b.region_id inner join tbl_country c on b.FK_country_id = c.country_id inner join tbl_continent d on c.FK_continent_id = d.continent_id left join tbl_destination e on a.city_id = e.FK_city_id left join tbl_hotel h on e.des_id = h.FK_des_id left join tbl_hotel_promotion_map p on h.hotel_id = p.hotel_id left join tbl_hotel_location_map l on h.hotel_id = l.hotel_id left join tbl_typeofstay_map t on h.hotel_id = t.hotel_id where 1=1 and c.country_code = 'thailand' and d.continent_code = 'asia' AND c.country_code = 'thailand' AND p.promotion_id = /** << Disabled on 2011-06-11 : problem with sort memory ....(Keeradit B.) solution: use distinct indtead >> group by a.city_id ,a.city_name, a.city_name_th ,a.city_code ,b.region_id ,b.region_name, b.region_name_th ,c.country_name,c.country_name_th ,c.country_code ,d.continent_code ,e.des_code,e.des_id,e.des_name,e.des_name_th **/ ) b /** << Disabled on 2011-06-11 >> group by country_id ,country_code **/