select * from (Select a.`postalCode`,a.`uniturl`, `region`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `source`, `property_type`, `address`,round(price) as price,minimum_nights, `rating`, `review_count`,`latitude`, `longitude`,case when propertyName='' then trim(address) else CONCAT(CONCAT(propertyName,'(',trim(address)),'',')') end as addre,propertyName,
(6371 *
acos(
cos( radians( 21.2759167 ) ) *
cos( radians( `latitude` ) ) *
cos(
radians( `longitude` ) - radians( -157.8233677 )
) +
sin(radians(21.2759167)) *
sin(radians(`latitude`))
)
) as distance from `vacationrentalindex` a inner join vacationrentalindexfeed b on a.uniturl=b.unitUrl and a.headline!='' and a.thumbnailUrl!='' and b.enabled=1 and b.blocked=0 ) as vw where 1 and round(price)>0 and bedrooms>=0 AND (`headline` like '%Hoolei%') AND sleeps>=1 order by rating desc LIMIT 0, 20
This property is hotel zoned, and not being affected by the recent proposed legislation.
Wailea beach, voted one of the top beaches in the World, is a short walk from Ho’olei Villas. This amazing beach has numerous activities such as ...
- 3 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Kihei
SECURE YOUR RESERVATION WITHIN MINUTES!
SAFE AND SECURE ON LINE BOOKING AND PAYMENT PROCESS!
24 HOUR PROFESSIONAL PROPERTY MANAGEMENT SERVICES!
HAWAII TAT #: W23624545-01
HO'OLEI VILLA: 69-2
CANCELLATION POLICY ...
- 3 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Kihei
SECURE YOUR RESERVATION WITHIN MINUTES!
SAFE AND SECURE ON LINE BOOKING AND PAYMENT PROCESS!
24 HOUR PROFESSIONAL PROPERTY MANAGEMENT SERVICES!
HAWAII TAT #: W23624545-01
HO'OLEI VILLA: 81-4
CANCELLATION POLICY ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
Hawaii> Kihei
Dramatic ocean views, this spacious town home is approximately 2500 sq ft of interior footage and 596 sq ft of lanai space. The Hoolei community is located directly across the street from the Four Seasons and the Grand Wailea Resort. Unit 31-2 ...
- 3 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Wailea-Makena
This property is hotel zoned, and not being affected by the recent proposed legislation.
Located in highly desirable secluded area of neighborhood your villa welcomes you with spacious floor plan and sweeping ocean views. Marvel at the ...
- 3 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Wailea
SECURE YOUR RESERVATION WITHIN MINUTES!
SAFE AND SECURE ON LINE BOOKING AND PAYMENT PROCESS!
24 HOUR PROFESSIONAL PROPERTY MANAGEMENT SERVICES!
HO'OLEI VILLA 93-1 is a brand new addition to the IPM Portfolio of fine luxury ...
- 4 bedroom
- 4 bathroom
- Sleeps 10
Hawaii> Kihei
Hawaii Life Vacations reminds you that Life is Grand at Ho'olei , an exclusive gated community located directly across the street and a short walking distance to the renowned Wailea Beach and the Grand Wailea Resort. Residence #93-3 is one of ...
- 3 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Wailea
Hawaii Life Vacations presents residence #23-3 (L3) at Ho’olei in the heart of the Wailea Resort in South Maui. Located on the third row of this deluxe townhome vacation rental community, the views are Vast and unobstructed, yet still feel ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
Hawaii> Wailea
SECURE YOUR RESERVATION WITHIN MINUTES!
SAFE AND SECURE ON LINE BOOKING AND PAYMENT PROCESS!
24 HOUR PROFESSIONAL PROPERTY MANAGEMENT SERVICES!
HAWAII TAT#: W67761941-01
HO'OLEI VILLA: 99-2
CANCELLATION POLICY ...
- 3 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Kihei
SECURE YOUR RESERVATION WITHIN MINUTES!
SAFE AND SECURE ON LINE BOOKING AND PAYMENT PROCESS!
24 HOUR PROFESSIONAL PROPERTY MANAGEMENT SERVICES!
HAWAII TAT #: W23624545-01
HO'OLEI VILLA: 81-3
CANCELLATION POLICY ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
Hawaii> Kihei
SECURE YOUR RESERVATION WITHIN MINUTES!
SAFE AND SECURE ON LINE BOOKING AND PAYMENT PROCESS!
24 HOUR PROFESSIONAL PROPERTY MANAGEMENT SERVICES!
HAWAII TAT#: TA-061-535-6416-01
HO'OLEI VILLA: 21-6
CANCELLATION POLICY ...
- 3 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Kihei