SELECT DISTINCT(p.propid),r.revs, p.reviews,if(p.reviews!="",p.reviews,r.revs) as newrev,p.category_id,category.name as cat_name,p.people_sleep,p.bedrooms,p.bathrooms,p.children,p.airport,p.features,p.pets,p.smoke,p.zip, p.wheelchair,p.prop_title,p.prop_desc,p.property_type,prop_date,display_calender,other_url_calender,is_feature,p.isilink_coid, p.isilink_prop_id, p.isilink, p.escapia, p.escapia_prop_id, p.min_rate, p.max_rate,p.min_week, p.max_week,p.min_month, p.max_month, p.review_rating, s.offer_start_date as special FROM property p LEFT JOIN ( SELECT propid,count(id) revs FROM review WHERE status = 1 GROUP BY propid ) r ON r.propid = p.propid LEFT JOIN special_offer s ON ( p.propid = s.propid ) LEFT JOIN category ON (p.category_id=category.id) LEFT JOIN effective_dates AS ed ON(ed.propId=p.propId) WHERE p.active IN("yes","d") AND p.category_id IN (2490,2491,2492,2493,2494,2495,2496,2497,2498,2499,2500,2501,2502,2503,2504,2505) Hamptons Vacation Rentals, Hamptons New York Vacation Rental Homes, NYvacationrentals.com
go
New York Vacation Rentals New York Hamptons Vacation Rentals

Featured Vacation Rentals in New York

 
Show Big Map Show Big Map
Availability
Price Range
Bedrooms
  • Studio (0)
  • 1 Bedroom (0)
  • 2 Bedrooms (1)
  • 3 Bedrooms (4)
  • 4+ Bedrooms (14)
Bathrooms
  • 1 Bathroom (0)
  • 2 Bathrooms (4)
  • 3 Bathrooms (4)
  • 4+ Bathrooms (11)
Sleeps
  • Sleeps 1-4 (0)
  • Sleeps 5-9 (6)
  • Sleeps 10-13 (2)
  • Sleeps 14-18 (9)
  • Sleeps 19-22 (2)
  • Sleeps 23 or more (0)
Property Type
  • House (14)
  • Mansion (3)
  • Condo (1)
  • Barn (1)
  • Cabin (1)
Popular Filters
  • Deal available (0)
  • Reviewed (0)
  • Book online (0)
Rental Manager
  • All (20)
  • Property manager (0)
  • Rental owner (20)

Hamptons Vacation Rentals (20)

List View Map View Favorites ()
Sort by:
New York Vacation Rentals | About Us | Owner/Manager Login
Advertise | Help | Contact Us | Privacy Policy | Terms Of Use
© Copyright 2000-Present New York Vacation Rentals, LLC. All Rights Reserved.