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 '%Hale Kamaole%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Hale Kamaole 159 is ready for your ideal Maui beach vacation! It is a recently renovated, exceptionally clean, 1 bedroom, 1 bath condo located in beautiful Hale Kamaole with pools and tennis courts, and you're right across from Kamaole Beach ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
This Modern, Maui Vacation Rental unit #176, has it all. The owner of this condo also owns unit 170, both of which have been completely remodeled from top to bottom recently. Occupancy of these units is limited to only 2 adults. Ocean & garden ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
This outstanding condo, situated in the Kihei Hale Kamaole Resort, features ocean views from every room! Watch the whales from our lanai. The two bedroom plus loft, split level unit has over 1300 square feet of living area.
Completely ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
**7 Night minimum stay required**
Ocean Views Forever – also overlooks beach. Unit just completely Upgraded and remodeled with high end materials, finishes and furniture. Master Bedroom has King Size Bed, spacious Master Bathroom and ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Remodeled, ocean view, ground floor 1 bedroom, 1 bathroom condo at Hale Kamaole. The location is perfect here just steps away from the pool or cross the street and end up at Kamaole III beach park, a local and tourist favorite for sunsets, ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Book with us and come to Maui to explore everything we have to offer!
Chic beautifully decorated condo conveniently located very close to world class beaches, shops, and restaurants! The unit offers an ocean view from the second story ...
- 1 bedroom
- 1 bathroom
- Sleeps 5
Hawaii> Kihei
Kihei-recently renovated condo., 1br (very comfortable new king size bed) & flat screen TV, 1 bath, living room w Queen size sofa bed ( just updated with a new sofa sleeper mattress), flat screen TV, wireless modem & high speed Spectrum ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Good Ocean View and mountain View. Very updated condo with extra spacious kitchen cabinets. New walk-in shower with remodeled bathroom cabinets. King bed in bedroom and queen hide-a-bed in living room. Rattan bedroom furniture. Comfy furniture ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
This Fabulous, Maui Vacation Rental unit #174, has it all. The owners of this condo also owns unit 176, both of which have been completely remodeled from top to bottom recently. Occupancy of these units is limited to only 2 adults. Ocean & ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Experience an upscale Maui vibe from the moment you enter this newly remodeled condo, Hale Kamaole 150, located directly across the street from one of South Kihei's best beaches, Kamaole III. With dramatic ocean views right from your private ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
This roomy and comfortable 1140 sq feet upper level condo has 2 bedrooms and 2 baths with a loft and can sleep 6. The unit features a king-size bed in the master bedroom, two twin beds in the downstairs bedroom, and a full-size bed in the loft. ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Beautifully updated condo with partial Ocean View! Upstairs master bedroom has island style King bed and furniture. Remodeled master bath has walk-in tiled shower, new vanity and granite top. Downstairs bedroom has island style furniture with a ...
- 2 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kihei
Tennis court, garden and Ocean View. This condo has a king bed in master bedroom, queen hide-a-bed in loft area. Living room has nice, comfortable, rattan furniture with an air conditioner in the living room. Downstairs bedroom has two twin ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Beautiful ground floor unit with Ocean View from lanai, close to the pool & easy access to BBQ. New wooden flooring.The unit sleeps 2 people. Unit 172’s living room area, offers comfortable couch and 2 sitting chairs, also a large flat screen ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Welcome to the highly sought after Hale Kamaole 121! This perfectly located ground floor unit offers beautiful sunsets and ocean views right from your lanai.
As you enter you are greeted by the open comfortable feel of this beautifully ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Hale Kamaole 120
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Hale Kamaole is a South Maui condominium complex located across the street ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Hale Kamaole #318 is a spacious 2 bedroom, 2 bath partial ocean view unit on the third floor. It boasts a fully-equipped kitchen, washer and dryer, free Wifi, and phone service with long distance calling to the US mainland and Canada. There are ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Book with us and come to Maui to explore everything we have to offer!
Beautiful condo conveniently located very close to world class beaches, shops, and restaurants! Gorgeous pool and ocean views second story unit with easy elevator ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Special rates for available dates in June !!! Hale Kamaole #170 is an elegantly renovated one bedroom, one bath, ground floor unit ideally located close to the legendary Kamaole III Beach Park. The unit is situated so you can enjoy your morning ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Come experience this beautiful beach style remodeled ground floor condo. It is located close to pool and up on the top part of the property. Luxury furnishings throughout the unit. Amazing walk in tile shower. King size bed in bedroom, and ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
