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 '%Nani Kai Hale%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Stunning luxury, ground floor condo directly on Sugar Beach, one of Maui's best beaches. The unit was completely remodeled floor to ceiling in 2017 and includes 2 bedrooms (1 king, 1 queen) each with en-suite bathrooms, a full luxury kitchen, ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
New Remodeled bathrooms!
With breathtaking ocean views right on the beach, this 5th floor beautifully decorated unit offers full beachfront access. The master bedroom features a California king bed with a en suite tiled walk-in shower. ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Nani Kai Hale 304 is a 1 bedroom, 2 bath condo with central air conditioning for your comfort. This well stocked, but uncluttered condo has everything you need for a relaxing vacation. Tile floors and neutral decor are perfect for the tropics. ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
WOW! Newly renovated and Updated! This showplace is on the fifth floor and features panoramic views of the Pacific Ocean and the West Maui Mountains! NEW windows ensure a QUIET and serene setting. The designer kitchen is open and has top of ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Nani Kai Hale 307 has an open floor plan that features a modern galley style kitchen and a spacious living room decorated with a tropical flair and with a queen sofa bed and LARGE flat panel screen tv. Split AC system installed in living room ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
302 is a spacious 2 bedroom, 2 bath renovated condo, perfect for a family getaway. Central A / C and double-pane glass keeps the condo cool and quiet. One bedroom has a king bed another has twin beds. The living room sectional converts into two ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Nani Kai Hale 601 is a 1 bedroom, 2 bath top floor condo with high efficiency split air conditioning. Light and bright tropical décor and a picture window facing the beautiful Pacific ocean. The kitchen is well equipped for preparing a quick ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Kihei
With breathtaking ocean views from the 4th floor, this beautifully remodeled unit offers full beachfront access. The master bedroom features a California king bed with a en suite tiled walk-in shower. There own access to bathroom #2 from living...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Kihei
The gorgeous view and amazing location will excite you at Nani Kai Hale 303, a third-floor condo featuring one bedroom, two bathrooms and spacious room for up to four guests. Enjoy the beautiful ocean view from the furnished lanai! This amazing ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Nani Kai Hale 405 is a 1 bedroom, 2 bath elegant yet casual condo that’s awaiting you. Enjoy your stay in this remodeled condo. Custom tile throughout, fully-stocked kitchen with blue pearl granite, unique desk area with internet, and flat ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Welcome to Nani Kai Hale 301, a wonderful 1 bedroom 2 full bath condo right on the Pacific Ocean! Enjoy views of both Haleakala and Molokini from the comfort of the air conditioned living room. Relax and dine in the fully remodeled kitchen and ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Located on the 4th floor, this unit sits on the beautiful and romantic white sand Sugar Beach. With renovations throughout and a Cal-King bed and TV in the bedroom, this unit is extremely comfortable. There is even a pull out queen sleeper sofa ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Beautifully furnished 2-bedroom, 2-bath located on Sugar Beach. Just minutes to shops, groceries and activities.
Nani Kai Hale #602 is a 2 bedroom, 2 bath top floor condo ideal for family getaways. The living room is air conditioned. The ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
