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 '%Makani A Kai%') AND sleeps>=1 order by rating desc LIMIT 0, 20
This 2nd story beach front condo offers direct beach access from complex grounds with views of the breathtaking Maalaea Bay, the grand volcano Haleakala and the sparkling Kihei lights at night. Enjoy your private lanai. The loft style primary ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Maalaea
Experience the ultimate tropical retreat at this gorgeous 2nd-story beachfront condo in Maui. With direct beach access just a 1-minute walk away, you can enjoy unspoiled natural beauty away from the crowds of other Maui beaches. Large native sea...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
This is a unique property on Maalaea Bay. Recently upgraded and renovated, it boasts wheelchair accessible features such as a large master bathroom, ADA compliant, including a large roll-in shower. Ground floor accessibility and very close ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Maalaea
This 2nd story beach front condo offers direct beach access from the complex grounds with views of the breathtaking Maalaea Bay, the grand volcano Haleakala and the sparkling Kihei lights at night. Enjoy your private lanai. The primary bedroom ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Maalaea
This ground floor level beach front condo offers direct beach access from the complex grounds with views of the breathtaking Maalaea Bay, the grand volcano Haleakala and the sparkling Kihei lights at night. Enjoy your private lanai and small ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Maalaea
Aloha! Nestled just steps away from the glistening shores of Maui, this one-bedroom, one-bathroom apartment is your perfect private oasis. With direct beach access, you can wake up to the sound of the waves and the soft embrace of the tropical ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Wailuku
Perched right where the waves meet the shore, this oceanfront haven in Wailuku captures Maui's magic from every angle. Through soaring windows that stretch from floor to ceiling, watch whales breach during winter months or catch the sky turning ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
