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 '%Kauhale Makai%') AND sleeps>=1 order by rating desc LIMIT 0, 15
This 1 bedroom, 1 bathroom condo was just completely remodeled. The Beach is right in front of the oceanfront property. Incredible sunset views year round and just minutes away from shopping, grocery stores, restaurants and golfing.
The unit is...
- 1 bedroom
- 1 bathroom
- Sleeps 3
HI> Kihei
Thank yourself with a stay in this newly remodeled condo at Kauhale Makai, the village by the sea. This remodel encompasses the stay-at-home charm of Hawaii with the added luxury of fresh and clean. Bask in the sun and AC while taking in the ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> KIHEI
Welcome to Kauhale Makai 433 with stunning ocean views! This spacious 2bd/2bath offers the perfect paradise getaway! Enjoy a gorgeous Maui sunset or a peaceful morning on your private lanai. Watch whales breaching, during season, or dine ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Kihei
Serene, sunny, renovated AIR CONDITIONED oceanfront condo in Central Kihei , renovated oceanfront condo has all the comforts of home and all the amenities of a vacation getaway. One bedroom KING SIZE BED/1 bath condo sleeps up to 4 persons. ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kihei
Enjoy the comforts of home in this well equipped unit with views from sunrise over Haleakala to sunset in the Pacific from the Lanai. The abundance of furniture in this unit makes sure you have enough options for comfort to make yourself at home...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> KIHEI
Simply amazing beach front suite with the most gorgeous direct ocean front views. Relax on your balcony with the cool ocean breezes while whales breach in the background. Listen to the waves roll in while you drift off to a peaceful night of ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Kihei
Enjoy Ocean Views from this one bedroom Condo. The bedroom has a queen bed and the living room has a sofa bed, allowing the unit to accommodate up to four (4) people. The living room features a new flat-screen TV and has a mixture of rattan ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> KIHEI
Treat yourself to tropical living in this 2-bedroom, 2-bathroom condo with elevators. With unobstructed views of the nearby ocean, huge windows, a private balcony, and a pool, it doesn’t get any better than this!
Kauhale Makai 535 is a nicely...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kihei
Guests rave about their accommodations and view from Kauhale Makai 506. You have a great view of the ocean from your balcony. Your condo is updated with hardwood floors, modern furniture, and all the little extras that will make your stay extra ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kihei
Wake up to the sight of the Pacific Ocean sparkling in the sunshine. Fall asleep to the lull of waves rushing onto the white-sand and coral Waipu’ilani Beach. And in between make memories that will last a lifetime. That’s what awaits you at ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kihei
Spacious and comfortable describe this ground floor condo with a beautiful garden view and partial ocean/mountain views. This unit, which sleeps 4, boasts a fully equipped kitchen, separate dining, central AC and a large bedroom with a ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> KIHEI
Enjoy Ocean Views from this one bedroom Condo. The bedroom has a queen bed and there is a pull-out sofa bed in the living room, allowing this unit to accommodate up to four (4) people. The fully equipped kitchen is perfect for making meals to ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> KIHEI
Enjoy a stunning view of the Pacific Ocean and the West Maui Mountains from this two (2) bedroom Condo. This unit has a queen bed in the master bedroom, two twin beds in the second bedroom and a pull out sofa bed in the living room, allowing ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> KIHEI
Newly renovated one-bedroom Ocean View Condo. Enjoy ocean and mountain views from the comfort of this gorgeous unit. The furnishings are a beautiful mixture of rattan and the bright pillows and upholstery create a lively atmosphere. The flooring...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> KIHEI
Completely Renovated and remodeled into the perfect modern beach style condo. Completed in May 2016, this trendy beach haven is the perfect getaway. Enjoy Ocean Views from this ground floor condo located next to the pool and center courtyard. ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> KIHEI