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 Banyans%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Maalaea Banyans #403 is a 2 bedroom, 2 bath unit on the top floor. It has a panoramic ocean view, a fully-equipped kitchen, window unit air conditioning in the living room and master bedroom, 3 flat-screen TVs, telephone service with long ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
Maalaea Banyans 416 offers stunning ocean views and is a real charmer, making for the perfect Hawaiian retreat. You can enjoy your morning coffee while watching a whale breach, especially during the season, or turtles almost nightly right from ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
Stunning Pacific Ocean Views! This perfectly located beach front property boasts over 6 miles of sandy shoreline ideal for long strolls on the beach! The Tradewinds Market is located down the street. Pick up the essentials to grill on the BBQ ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Wailuku
Enjoy being lulled to sleep by the sounds of the surf and the palm leaves swaying in the cool tropical breezes in this charming ocean front condo. Private lanai showcases a beautiful view that can be seen from the living room and kitchen. Also ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailuku
Maalaea Banyans 418 direct ocean front location will be the jewel of your stay. Located on the shores of the Pacific, the full panorama from your lanai captures the ever changing spectacles of the beautiful blue Pacific ocean. Humpback whales...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
Maalaea Banyans 207
Experience breathtaking views of the pristine waters of the Pacific - feel a sense of serenity as you listen to the gentle surf rolling on the shore. Enjoy colorful Maui sunsets every night while whale watching (in ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Wailuku
Dreaming of a Maui vacation without breaking the bank? This budget-friendly condo offers a simple and cozy retreat for two, just steps from the ocean in the serene community of Maalaea. Conveniently located near Kihei, Lahaina, and the airport, ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Wailuku
