Find a Roofer in Colchester, Essex

SELECT a.memberID, a.memberName, a.memberURL2, a.memberImage, a.memberDescr, 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, d.secondStageInterview FROM ( SELECT memberID, memberName, memberURL2, memberImage, memberDescr, townID, isVerified, liabilityFile, liabilityExpiry, idFile, addressMatched FROM members WHERE isActive=1 AND memberID IN (SELECT memberID FROM listings WHERE expiryDate>NOW() AND listingID IN (SELECT listingID FROM listingcats WHERE categoryID=57)) AND memberID IN (SELECT memberID FROM listings WHERE listingID IN (SELECT listingID FROM (SELECT *, MIN(( 3959 * acos( cos( radians(51.89234) ) * cos( radians( listinglocations.latitude ) ) * cos( radians( listinglocations.longitude ) - radians(0.89787) ) + sin( radians(51.89234) ) * sin( radians( listinglocations.latitude ) ) ) )) AS distance FROM listinglocations GROUP BY listlocID HAVING (distance <= 30 OR listinglocations.countyID=62) 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 FROM listings WHERE listingType>=0 AND expiryDate>NOW() GROUP BY memberID ) c ON a.memberID=c.memberID LEFT JOIN ( SELECT memberID, 1 AS secondStageInterview FROM listings WHERE listingType>=0 AND upgrdSpokenTo=1 GROUP BY memberID ) d ON a.memberID=d.memberID GROUP BY a.memberID ORDER BY isVerified DESC, listingType DESC