Find a Builder in Coalhall, East Ayrshire

SELECT a.memberID, a.memberName, a.memberURL2, a.memberImage, a.memberDescr, a.categoryString, a.townID, b.countyID, COALESCE(c.isPaid, 0) AS isPaid, c.listingType, a.isVerified AS premierMember, b.place_name, b.county, a.liabilityFile, a.liabilityExpiry, a.idFile, a.addressMatched, CASE WHEN c.upgrdSpokenTo=1 THEN 1 ELSE 0 END AS secondStageInterview FROM ( SELECT memberID, memberName, memberURL2, memberImage, memberDescr, townID, isVerified, liabilityFile, liabilityExpiry, idFile, addressMatched, categoryString FROM members WHERE isActive=1 AND CONCAT(',', categoryIDs, ',') LIKE ',%48%,' AND memberID IN (SELECT memberID FROM listings WHERE listingID IN (SELECT listingID FROM (SELECT *, MIN(( 3959 * acos( cos( radians(55.44460) ) * cos( radians( listinglocations.latitude ) ) * cos( radians( listinglocations.longitude ) - radians(-4.45989) ) + sin( radians(55.44460) ) * sin( radians( listinglocations.latitude ) ) ) )) AS distance FROM listinglocations GROUP BY listlocID HAVING (distance <= 30 OR listinglocations.countyID=55) ORDER BY distance) z)) ) a INNER JOIN areas b ON a.townID=b.id LEFT JOIN ( SELECT memberID, 1 AS isPaid, MAX(listingType) AS listingType, MAX(expiryDate) AS expiryDate, MAX(upgrdSpokenTo) AS upgrdSpokenTo FROM listings WHERE listingType>=0 AND expiryDate>NOW() GROUP BY memberID ) c ON a.memberID=c.memberID GROUP BY a.memberID ORDER BY isVerified DESC, listingType DESC