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, 20
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 ...
- 1 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
Welcome to our luxurious retreat of Hale Mahina, where an exclusive and refined experience awaits. Nestled in the heart of Princeville on the enchanting North Shore of Kauai, this meticulously designed two-bedroom sanctuary spans an expansive ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Hale Mahina Turtle Condo 🐢
This is a beautifully decorated turtle one-bedroom condo situated on a very special spot-on Maui. Private beach with beautiful views of Lanai and Molokai and magnificent sunsets. Hale Mahina ("Ha-Lay ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
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
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
OUR UNIT: "PARADISE ON THE BEACH": A206 HALE MAHINA***
MOST BEAUTIFUL LARGE ONE BEDROOM ONE BATHROOM DIRECT VIEW ON THE OCEAN. "A" BUILDING HAS THE 100% OCEANFRONT & SUNSET VIEWS!... "B" Building does not**
**ZERO HOST ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
OUR UNIT: "PARADISE ON THE BEACH": A105 HALE MAHINA, WALK RIGHT OUT TO THE OCEAN GROUND FLOOR UNIT & YOUR VERY OWN PRIVATE BEACH AT HALE MAHINA!
**ZERO HOST FEES!!**ONLY CLEANING FEE**NO STEPS OR ELEVATOR NEEDED**
"A" BUILDING HAS...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
INCREDIBLE WALKOUT UNIT TO OCEAN & SANDY BEACH-HUGE 1400 SQ FT, 2 BEDROOM 3 BEDS/2 BATHROOM GROUND FLOOR WALKOUT TO OCEANFRONT.
A101 HALE MAHINA. **You Sleep right on the Ocean, walk right out to the Ocean, and your Private Beach**ZERO ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
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 Beach Resort in beautiful West Maui! Hale Mahina, which translates to "House of the Moon," is situated on the renowned West Coast of Maui, nestled between the West Maui Mountains and surrounded by the enchanting oceanfront...
- 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 3
Hawaii> Lahaina
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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Maui Hawaii - Private Sandy Beach - Turtle Condo 🐢
Private beach with beautiful views of Lanai and Molokai and magnificent sunsets.
Hale Mahina ("Ha-Lay Ma-hee-na") is one of the most sought after private beaches in the ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Hale Mahina B-205 has it all. From your lanai you 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. Located less than 40 yards from the ocean,...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
*This listing consists of two separate units.
**Please inquire before booking. Dates are not confirmed until Owner/ Manager sends confirmation. Calendars are not live or accurate due to multi agents and units.
***TRIP INSURANCE IS HIGHLY ...
- 2 bedroom
- 2 bathroom
- Sleeps 8
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 ...
- 4 bedroom
- 6 bathroom
- Sleeps 12
Hawaii> Koloa
Honokowai Surfer Condo: Newly remodeled kitchen with new fridge, washer and dryer, bathroom shower, surfing theme, surfboards and other updates.
This is a beautiful one-bedroom condo situated on a very special spot-on Maui. Private ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
