Luxury Holiday Homes, Apartments, Holiday Cottages and Large Country Houses in Carmarthenshire, Wales

select s.*, hs.hub_id , tl.photo_thumb as photo_thumb_tl, tl.description as description_tl from luxury_static_ads as a left join luxury_static_searchpages as s on s.id=a.id left join luxury_static_searchpages as tl on tl.script_name=s.script_name and tl.level=0 left join searchpage_hubs as hs on hs.searchpage_id=s.id left join hubs as h on hs.hub_id=h.id LEFT JOIN ( SELECT searchpage_id, COUNT(*) AS hub_count FROM searchpage_hubs GROUP BY searchpage_id ) AS hub_counts ON hub_counts.searchpage_id = s.id where a.cid=67 and s.id<>221 and s.level=2 and hub_counts.hub_count>0 ORDER BY s.pagetype_id asc, COALESCE(hub_counts.hub_count, 0) ASC Luxury Holiday Homes, Apartments, Holiday Cottages and Large Country Houses in Carmarthenshire, Wales