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, 50
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, ...
- 2 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Wailuku
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
Sleep to the sound of the lapping surf on the shore below Maalaea Banyans 411, which features forever, spectacular penthouse level ocean views from the living room and lanai. Located beach front adjacent to picturesque Maalaea Harbor, this ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
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 ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
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
