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 '%Maalaea Kai%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Enjoy this 2nd floor unit featuring gorgeous views throughout the unit of Maalaea Bay, the grand volcano Haleakala and the sparkling Kihei lights at night. The unit is tastefully decorated with the spirit of Aloha and has a portable a/c unit in ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Wailuku
Welcome to our nicely remodeled 3rd floor unit featuring gorgeous views throughout the unit of Maalaea Bay, the grand volcano Haleakala and the sparkling Kihei lights at night. The unit is tastefully decorated with the spirit of Aloha and has a ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
Spectacular Ocean Views at Maalaea Kai
Clean, well-maintained Maui two-bedroom, two-bath condo away from the crowds, with fantastic views, located in Maalaea Bay with easy access to local beaches and towns.
This condo is our ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Maalaea
GROUND LEVEL TWO BEDROOM, TWO BATH CONDO ON GROUND LEVEL WITH STUNNING VIEW OF OCEAN. UPGRADED KITCHEN AND BATHROOMS. IN UNIT LAUNDRY AND AIR CONDITIONING. MASTER HAS A CALIFORNIA KING AND SECOND BEDROOM HAS A QUEEN AND XL TWIN. PERFECT FOR ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailuku
Book 5 nights and enjoy the 6th night free! Valid for new reservations from 10/13/24 for stays between 10/13/2024 - 11/21/2024!
Stunning Pacific Ocean Views as you enter Maalaea Kai 215! This perfectly located beach front property boasts ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Wailuku
Enjoy the beautiful seascapes, surrounding islands and lovely Hawaiian skies above meticulous, palm-lined grounds. Winter whale season from approximately the end of Nov to mid April. Catch the occasional view of resident turtles from the lanai....
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Top Floor two bedroom, two bath unit in Maalaea Kai. Watch the sunrise over Haleakala and see the turtles swim in the bay directly in front of you. Come during whale season and see them from your lanai.
Maalaea is a small “ village” and ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
Soak in this Third floor unit featuring gorgeous views throughout the unit of Maalaea Bay, the grand volcano Haleakala and the sparkling Kihei lights at night. The unit is a new listing and tastefully decorated with central AC throughout to keep...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Maalaea
