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 '%Kaleialoha%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Gorgeously and recently renovated unit at Kaliealoha tucked in the corner to avail of the amazing ocean views West Maui has to offer. It’s arguably the best unit at Kaleialoha. Entire left wall has tinted widows for privacy, feels like your ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
Maui north of Lahaina in Honokowai. Our Condo complex is situated on ocean front property with a beautiful reef. Our unit is about 30 feet from our front door to the ocean. Our Ohana (extended family home) has the mystical West Maui mountains ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
Enjoy spectacular views from this top floor oceanfront condo with new split-system A/C in both living room and bedroom! If you prefer nature instead of air conditioning, just open the windows and sliding doors and let the trade winds drift ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
OCEANFRONT, one bedroom, one bathroom unit at Kaleialoha
Pool and BBQ area
Accommodate up to 4 guests
Air conditioned unit
Free Parking and No resort fees
Incredible Ocean Front location. This one bedroom, one bath condo comes...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
OCEANFRONT, ground floor one bedroom, one bathroom unit at Kaleialoha
Pool and BBQ area
Accommodate up to 4 guests
Air conditioning unit
Free Parking and No resort fees!
VIEW, VIEW, VIEW! Upgraded, Direct OCEANFRONT, ground ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaian
Step into this upgraded direct oceanfront condo and discover a haven of comfort and luxury. Relax on a plush king-size bed surrounded by newly furnished interiors. The kitchen features granite countertops, stainless steel appliances, and modern ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Welcome to Kaleialoha 413. This top floor, direct oceanfront condo features 180 ocean & island views. This condo has a remodeled kitchen & bathroom, AC in living area, 2 Flat screen TVs, new king bed with luxury bedding and more! Close to shops,...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Napili-Honokowai
