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, 30
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 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
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 with easy elevator access, proximity to the pool, and lots of parking. Relax on ...
- 1 bedroom
- 1 bathroom
- Sleeps 5
Hawaii> Kihei
This Maui vacation condo has a Contemporary Hawaiian style.The owners of this condo prefer only 2 adults and no large group/wedding party rentals. Starting in the living room enjoy the comfortable stuffed sitting chairs. A 46 inch HD Tv in the ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Kihei-recently renovated condo., 1br (very comfortable king size bed) & flat screen TV, 1 ba, living room w Queen size sofa bed ( just updated with a new sofa sleeper mattress), flat screen TV, wireless modem & high speed Spectrum internet ...
- 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 Modern, Maui Vacation Rental unit #176, has it all. The owners of this condo, also own units 174 & 170 which have all 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
This Fabulous, Maui Vacation Rental unit #174, has it all. The owners of this condo, also own units 170 & 176 which have all 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
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 2 ...
- 2 bedroom
- 2 bathroom
- Sleeps 8
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
Beautiful condo conveniently located very close to world class beaches, shops, and restaurants! Gorgeous pool and ocean views second story unit with easy elevator access and lots of parking. Relax on your balcony with the cool ocean breezes ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
This meticulously updated and well maintained air conditioned 2 bedroom with loft has everything you need to relax in luxury! Conveniently located very close to world class beaches, hiking, shops, and restaurants! Absolutely gorgeous pool view...
- 3 bedroom
- 2 bathroom
- Sleeps 7
Hawaii> Kihei
Unit 144 is a bright and spacious one bedroom, one bathroom, ground floor unit with a lovely landscaped and expanded private lanai. The unit has been extensive remodeled and updated.
Unit 144 features a large, semi-private lanai with a ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
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
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
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 8
Hawaii> Kihei
Great Ocean and Garden Views. This two bedroom unit offers king beds in both the upper and lower bedrooms which is great for couples vacationing together. The remodeled kitchen has an elongated larger kitchen area with very nice wood cabinets ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> 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 2024 upgrades. All new expanded kitchen with
preparation and dining island, all new black ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> 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
Hawaii> Kihei
One-bedroom ground floor condo with Garden View and easy access to two pools and BBQ area. King bed in bedroom, with Murphy bed in living room - to sleep 4 people comfortably. Totally remodeled unit, new appliances, furniture and bedding ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> 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 4
Hawaii> Kihei
This one bedroom condo has a Ocean & Garden View. Artistically decorated with beautiful tiles used in kitchen and bathroom, walk-in tile shower. Updated completely in Hawaiian style. King bed in bedroom with queen hide-a-bed in living room. ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Kihei-Recently renovated condo., 1 br (very comfortable king size bed) & flat screen TV, 1 ba, living room w Queen size sofa bed, large flat screen TV, wireless modem & high speed internet. Ceiling fans in bedroom & living room. New A/C in ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Prime Maui location where the best beaches are and the best location in south Kihei.
Beautiful ground floor, newly remodeled (king) bedroom with all the amenities, sleeps 2.
Kamaole III beach is less than a minute walk, just across the ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
