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 '%Lauloa%') AND sleeps>=1 order by rating desc LIMIT 0, 20
This nicely remodeled 3rd-floor condo offers breathtaking views of Maalaea Bay, the grand volcano Haleakala, and the sparkling Kihei lights at night from the private lanai! The primary suite features a comfy king bed and ensuite bathroom with a ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
This updated and spacious 3rd-floor condo offers breathtaking views of Maalaea Bay, the grand volcano Haleakala, and the sparkling Kihei lights at night from the private lanai! The primary suite features a comfy Cal king bed and ensuite bathroom...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Maalaea
Welcome to your dream Maui getaway! This highly updated 2-bedroom, 2-bath condo in Maalaea offers the perfect blend of modern comfort and breathtaking oceanfront views making it an ideal haven for families or friends seeking an unforgettable ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailuku
This updated 2nd-floor condo offers breathtaking views of Maalaea Bay, the grand volcano Haleakala, and the sparkling Kihei lights at night from the private lanai! The primary suite features a comfy king bed and ensuite bathroom with a walk-in ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Wailuku
Two bedroom-two bath condo on second floor of Lauloa. Walk to end of block and visit the Maui Ocean Center, several restaurants and shops, or take a cruise from the harbor with the Pacific Whale Foundation.
At the other end of the block you ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
This spacious and quiet 3rd-floor condo just steps from the ocean offers three bedrooms and stunning panoramic views of the Pacific and southwest Maui with brilliant sunrises over the Haleakala volcano each morning. The primary suite has a comfy...
- 3 bedroom
- 2 bathroom
- Sleeps 7
Hawaii> Maalaea
Summary:
Welcome to our Maui Oceanfront vacation condo! With breathtaking ocean views and all the amenities you need, it's the perfect home base for your Maui adventure.
The Space:
Welcome to your dream Maui vacation rental! Our ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
