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 '%Hale Mahina%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Welcome to Hale Mahina A-302! With a direct oceanfront view and located on a secluded and gated white-sand beach, our condo is perfect for those looking for a quiet and relaxing West Maui getaway.
Our favorite thing about this condo is ...
- 2 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Lahaina
Hale Mahina is a beautiful 4 bedroom, 3.5 bathroom home located in a private, very quiet neighborhood in the hills of Pupukea, on Oahu's North Shore. Enjoy panoramic ocean views while still being within minutes of first-rate beaches and other ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Haleiwa
West Maui is open and welcoming guests! Experience your own island Aloha on Maui! Our newly renovated condo is located right on the beach at the beautiful Hale Mahina Beach Resort - one of the most sought after beach resorts in West Maui. It is...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Welcome to Your Exclusive Oceanfront Haven at Hale Mahina, Unit A206.
Dive into the essence of beachfront luxury in our elegantly remodeled Unit A206 at Hale Mahina. Nestled in the sought-after A-building, this one-bedroom, split-level ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Absolutely beachfront, 1 bedroom condo just 20 meters from the ocean. Corner unit on the second floor of a four-story private beachfront complex giving you a spectacular, unobstructed, wrap around ocean view and memorable sunsets. Sleeps 4 ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Napili-Honokowai
Welcome to Hale Mahina #B-405, a stunning oceanfront condo located in the highly sought-after Hale Mahina Beach Resort on West Maui. This top-floor unit offers breathtaking views of the Pailolo Channel and the neighboring islands, perfect for ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina, Maui
Guest(s) must review and sign host rental agreement and provide government issued ID to host prior to reservation acceptance. Minimum age of main guest must be 28 years. Condo is not wheelchair accessible and/or handicap accessible. Two person ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
Welcome to Hale Mahina B403. This beautifully remodeled top floor beachfront 2 bedroom, 2 bath condo boasts expansive ocean views & classic Hawaiiana decor. This condo features a remodeled kitchen with stainless appliances, office desk, & ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Napili-Honokowai
Just steps away from the beach this ground floor direct ocean front view unit is a great pick for your Hawaiian vacation. This condo features a newly remodeled kitchen and bathroom, tiled throughout. Condo includes full size washer and dryer as ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Situated in the exclusive Kukui'ula neighborhood on Kauai's sun-drenched south side, this exceptional Koloa vacation rental home creates the ideal setting for your group to experience indoor-outdoor island living. Hale Mahina Hou boasts a ...
- 5 bedroom
- 6 bathroom
- Sleeps 12
Hawaii> Koloa
