SELECT
CMT.*, CNT.country_name, ST.state_name, ST.state_code, CT.city_name, CAT.name as category_name, CAT.uri_slug as category_uri_slug,
(SELECT GROUP_CONCAT(category_id) FROM community_categories WHERE community_id = CMT.id ) as category_id_grp,
(SELECT file_name FROM community_images WHERE community_id = CMT.id AND featured=1) as featured_image,
(SELECT price FROM community_pricing WHERE community_id = CMT.id ORDER BY price ASC LIMIT 1) as starting_price,
(SELECT GROUP_CONCAT(room_type) FROM community_pricing WHERE community_id = CMT.id) as room_type_grp,
(SELECT GROUP_CONCAT(state_id) FROM community_locations WHERE community_id = CMT.id) as state_id_grp,
(SELECT GROUP_CONCAT(city_id) FROM community_locations WHERE community_id = CMT.id) as city_id_grp
FROM communities as CMT LEFT JOIN categories as CAT ON CAT.id = CMT.category_id LEFT JOIN countries as CNT ON CNT.id = CMT.country_id LEFT JOIN states as ST ON ST.id = CMT.state_id LEFT JOIN cities as CT ON CT.id = CMT.city_id WHERE CMT.featured = '1' AND CMT.status = '1' ORDER BY RAND() LIMIT 6