select * from (Select a.`postalCode`,a.`uniturl`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `region`, `source`, `property_type`, `location_type`, `suitability`, `features`, `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) 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, 15
This super-modern ground floor condo has been completely remodeled for your comfort and style. Kitchen has beautiful wood cabinets with granite counter tops for easy clean-up. Granite top is used as a dining set with tall comfortable rattan ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> kihei
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 large flat screen TV. Downstairs Bedroom ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> 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
HI> Kihei
Welcome to South Maui, home of consistent sunny weather! 327 Hale Kamaole (Kamaole Ocean Views) welcomes you to a beach community where you can enjoy the ocean view and spacious accommodations. Hale Kamaole boasts more than 7 acres of ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
HI> Kihei
Extremely nice two bedroom Maui vacation condo with a slight Ocean, garden and pool views. Very modern and clean. Refurbished and modernized in 2014 with beautiful solid bamboo flooring installed throughout, completely repainted and the purchase...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Kihei
Aloha & welcome to our Hawaiian Home, and your vacation beach haven! Newly renovated, we hope you enjoy our elegant, spacious 'beach home' - a ground floor condo, located in the beautiful Hale Kamaole resort, close to the vibrant heart of the ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kihei
Hale Kamaole 151
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 from Kamaole ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> 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 from Kamaole ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kihei
Hale Kamaole 167
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 from Kamaole ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kihei
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
HI> Kihei
Located in the heart of South Kihei, Maui, this unit has a Fantastic Ocean/Garden View. Just
remodeled and includes new furnishings from recent 2019 upgrades. All new expanded kitchen with
preparation and dining island, all new black ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> 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
HI> 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
HI> Kihei
This two bedroom condo without a loft, has a good Ocean and Garden View. Master bedroom has king bed. Down stairs bedroom has king bed. Spacious kitchen cabinets. Wood and tile floor including lanai no carpet. This condo has lots of extra ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Kihei
This Maui condo rental, has two bedroom’s plus a sleeping loft featuring Ocean, garden, and pool views ! Master bedroom has king bed. Unit has flat screen TVs & Blue-ray players in both bedrooms, a large flat screen/Blu-ray player in living ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Kihei
