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 '%Menehune Shores%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Enjoy sun-soaked beach days and relaxing balmy evenings at this fourth-floor 2BR/2BA Menehune Shores condo. Your rental offers a prime location on the right side of the building, overlooking the pool and ocean and offering split system remote ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Nani Kai Escape is a 6th Floor Oceanfront Penthouse with breathtaking ocean views offering the perfect Hawaiian getaway. Maui is known for it's world-famous beaches so come check that off your bucket list and literally stay steps from the beach....
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Enjoy expansive ocean views from the lanai at this bright and beautifully remodeled oceanfront one bedroom in the heart of Kihei town.
Take a dip in the heated pool, or enjoy long walks and a swim at the protected beach just steps from ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
E Komo Mai to our oceanfront unit on the 4th floor with stunning ocean view at the Menehune Shores. Feel the ocean breeze, listen to the waves, and watch the whales (during winter time) and sunsets from our tranquil, oceanfront condo. From our ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Highly cleaned and sanitized for your vacation. Ask about our AlohaSafeStay program.
If you have a stay that qualifies for housekeeping services, traditional housekeeping service will be performed every 7th day. This includes refreshing...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei, Maui
MENEHUNE SHORES is a peaceful, immaculately tended, oceanfront complex located on the beach in north Kihei, a twenty minute drive from the Kahului airport. Shopping, restaurants, and excursion opportunities are just minutes away. Our ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
Hawaii Life Vacations presents a stunning new addition to our Hotel Collection. This oceanfront 1 bd / 1 ba condo was recently remodeled and offers a private lanai overlooking the beach just steps away.
Take a dip in the heated pool, or ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Highly cleaned and sanitized for your vacation. Ask about our AlohaSafeStay program.
If you have a stay that qualifies for housekeeping services, traditional housekeeping service will be performed every 7th day. This includes refreshing...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei, Maui
Forth floor 2 bed/2bath recently renovated condo, Menehune Shores oceanfront. Enjoy ocean and beach views from your balcony. The beach is just a few steps away from our condo. Enjoy the sunsets and sunrises. Our guests say the beach in front ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Highly cleaned and sanitized for your vacation. Ask about our AlohaSafeStay program.
If you have a stay that qualifies for housekeeping services, traditional housekeeping service will be performed every 7th day. This includes refreshing...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei, Maui
Highly cleaned and sanitized for your vacation. Ask about our AlohaSafeStay program.
If you have a stay that qualifies for housekeeping services, traditional housekeeping service will be performed every 7th day. This includes refreshing...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei, Maui
Upgraded direct oceanview condo at Menehune Shores #306 with views, views, views! Enjoy watching the sunset, seasonal whale watching, outriggers, paddle-boarders and kite boarding from the comfort of your private oceanfront Lanai. Many ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
Located front and center on the fourth-floor, this spacious 3BR/2BA Menehune Shores condo offers unbeatable views of the sparkling ocean below. Enjoy the sounds of the waves lapping the shore as you are situated toward the ocean side and far ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kihei
Enjoy amazing sunsets from your private lanai, or the comfort of the reclining sofa in the living room. TVs in living room & bedroom.
Fully equipped kitchen with all major appliances, cookware, dining ware, and utensils. Take a stroll or find...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Kihei
Completely Remodeled Ocean Front two-bedroom condo located at the Menehune Shores in Kihei creates the perfect spot for your Maui getaway. The lanai is a great place to relax and enjoy the ocean views. The beach in front of the property is great...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
Book with us and come to Maui to explore everything we have to offer!
This amazing, light and bright, oceanfront, 2-bedroom, 2-bath, Menehune Shores Resort condominium was remodeled in April 2021 and has a polished, modern beach vibe. ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
This beautifully updated coastal condo features 3 bedrooms, 2 bathrooms & a private lanai. The condo has a new split AC system & can accommodate up to 8 people. Admire breathtaking ocean views, sunsets, sailboats, kite surfing, & whale ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kihei
Menehune Shores #401 is a 4th floor unit that is a beautifully furnished 2 Bedroom 2 Bath unit that can sleep 6. It has a fully equipped kitchen, washer and dryer, ceiling fans and a giant flat screen TV. New (2022) Split System AC's throughout....
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
This condo is a beautiful second floor condo with a beautiful view of the ocean.
You can head up to the roof top to enjoy the full kitchen, gas BBQ grills, picnic tables plus a 360 degree view of West Maui. Or, head downstairs to enjoy ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Gorgeous Ocean Front views! This two-bedroom condo has spectacular Ocean Front views that are visible from most of the condo. Enjoy waking up with fabulous views of the ocean from the master bedroom. The fully-equipped kitchen has everything you...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
