troubleshooting Question

Distinct Record from joined table

Avatar of Nico2011
Nico2011Flag for United Kingdom of Great Britain and Northern Ireland asked on
ASPMicrosoft SQL Server 2008
16 Comments1 Solution452 ViewsLast Modified:
Hello Experts,

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.
ASKER CERTIFIED SOLUTION
jogos

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 16 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 16 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros