Please can let me know how I would retrieve a unique record fro a joined table please?
My query is:
SQL = "select Diary.*, x.* from (select row_number() over (order by v.distance) rn, v.* from (SELECT ID as XID, PayMonthly, ccaccepted, pool, sattv, disabled_access, internet, restaurants, shops, beach, golf, skiing, tennis, hiking, for_rent, for_sale, price_low, rental_currency, rental_USD, rental_EUR, rental_GBP, Problem, Property_Name, Town, Region, Property_Type, Property_Description, short_description, bedrooms, sleeps, photo, photo_two, photo_three, thumb, lat, lng, (3960 * acos( cos( radians(43.64701) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(7.00241) ) + sin( radians(43.64701) ) * sin( radians( lat ) ) ) ) AS distance FROM Villas WHERE for_rent = 1 AND Problem = 0 AND price_low IS NOT NULL) AS v WHERE v.distance <= 10 ) AS x LEFT JOIN Diary on Diary.PropID = x.XID where Diary.StartDate not between '01-Jul-2012' and '28-Jul-2012' AND Diary.StartDate"
and I need to return only one record for each property when the Diary is queried - at the moment, I get a record for each booking in the diary, so if a property has had 5 bookings, it comes up 5 times...
Thanks in advance.