select * from (Select a.`postalCode`,a.`uniturl`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `region`, `source`, `property_type`, `location_type`, `suitability`, `features`, `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) as vw where 1 and round(price)>0 and bedrooms>=0 AND (city = 'kahana' OR postalCode = 'kahana' OR region like '%|kahana|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Treat yourself to this direct oceanfront tropical getaway at Kahana Sunset Resort. When you enter this beautifully remodeled 2 bedroom/2.5 bath condominium, you will be immediately captivated by the breathtaking views of the Pacific ocean. ...
2 bedroom
3 bathroom
Sleeps 6
HI> Kahana
We just finished a complete remodel of the kitchen and the living room, yes everything is new. If you are looking for future dates that are blocked contact me.
Stop!! You found it!! The best studio condo at the Royal Kahana based on the view,...
0 bedroom
1 bathroom
Sleeps 2
HI> Kahana
Kahana Sunset C2C - This spacious unit is ideal for a couple or family/group of up to four people. The open concept floor plan has all of the conveniences of home. Air conditioned and fully remodeled, one bedroom / one bath.
The jewel of ...
1 bedroom
1 bathroom
Sleeps 4
HI> Kahana
This beachfront 3-bedroom condominium is one of the finest in West Maui. Comfortably sleeping up to six guests, this completely renovated unit is spacious, bright and beautiful.
Features like ceramic tile floors, custom stone and glass block ...
3 bedroom
2 bathroom
Sleeps 8
HI> Kahana
Come stay in our luxurious 3rd FLOOR, WRAP-AROUND CORNER OCEANFRONT condo, and you will never want to leave! It is very spacious, with 1400 sq ft inside, consisting of 2 bdrm 2.5 bath and is fully furnished with TWO NEW AC UNITS. The condo is...
2 bedroom
3 bathroom
Sleeps 6
HI> Kahana
Located on the secluded and sandy beach of Keoni Nu'i Bay, Kahana Sunset Resort is the ideal beachfront Maui location. With panoramic views of the islands of Molokai and Lanai, our beach is ideal for any of your favorite beach activities - ...
1 bedroom
1 bathroom
Sleeps 4
HI> Kahana
Beautiful - Oceanfront - Ground Floor - Walk Out
Fabulous updated oceanfront condo location: Kuleana 607 is right on the ocean on Maui's pristine West Coast with gorgeous panoramic views of the Pacific Ocean and the lovely off-shore islands of ...
1 bedroom
1 bathroom
Sleeps 4
HI> Kahana
Kahana Sunset B9 is an upgraded oceanview air-conditioned two bedroom / two bath condominium with spectacular views of the Pacific from both the living room and master bedroom lanai. Air Conditioning is available and is optional - $25.00 per ...
2 bedroom
2 bathroom
Sleeps 6
HI> Kahana
This perfectly located condo is in the heart of Kahana, is just a short walk to a sandy beach and 5 min drive to Napili Beach. Many of the favorite shops and restaurants are located within 5 minuets of our new remodeled condo. From the spacious ...
1 bedroom
1 bathroom
Sleeps 3
HI> Kahana
Wake up each morning to views of the beautiful Pacific and enjoy a breakfast filled with tropical fruit and fresh island coffee on your private lanai in this comfortable ocean view condominium. Unit 1209 is designed specifically for a couple ...
1 bedroom
1 bathroom
Sleeps 3
HI> Kahana
Kahana Sunset Resort B10
Enjoy the beautiful expansive views of the Pacific when staying in this spacious two level 2 bedroom Kahana Sunset condominium. Relax on either of your lanais and watch whales play in the warm waters of the Pacific ...
2 bedroom
3 bathroom
Sleeps 6
HI> Kahana
Royal Kahana is a beachfront resort on the West side between the Ka'anapali and Kapalua! This studio unit has King bed and twin sleeper sofa. Fully equipped kitchen with granite counters tops and full size appliances. Flat screen high ...
1 bedroom
1 bathroom
Sleeps 3
HI> Kahana
Kahana Outrigger is a small oceanfront 16 unit low rise complex with shopping and restaurants nearby. Just 10 minutes from the historic whaling town of Lahaina on West Maui. The lanai has ample space for outdoor dining, whale watching ...
3 bedroom
2 bathroom
Sleeps 8
HI> Kahana
Tropical landscaping, sparkling heated swimming pool and delightful surprises, like the sandy beach of Keoni Nu'i Bay, surround you while staying at Kahana Sunset. Unit C8 has been fully renovated and features granite countertops, tile ...
2 bedroom
2 bathroom
Sleeps 6
HI> Kahana
