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 '%icc%') AND sleeps>=1 order by rating desc LIMIT 0, 20
International Colony Club 20 is a charming, rustic plantation style cottage in Kaanapali is the perfect place to completely unplug and relax, or treat as your home base for all the daytime and nighttime activities of West Maui! With a ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Aloha, and welcome to our comfortable Hawaiian plantation style cottage located at the International Colony Club in beautiful West Maui. Situated just across the road from the world-famous Kaanapali Beach Resort Area, our charming cottage offers...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Aloha and welcome to International Colony Club 22. Cottage 22 is a special place where travelers keep coming back and keep it a secret! With Hawaiian Style on the exterior and modern charm on the inside. Our focus is comfort and the quality of ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Lahaina
International Colony Club 34 is a charming and quiet cottage just a short walk to the beach! This quaint vacation home comes equipped with a fully equipped kitchen great for cooking meals to enjoy with the family at the large dining table or ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Lahaina
Imagine a day filled with sun, surf and snorkeling at one of the best spots on Maui, just minutes from your private beach bungalow. After a full day of adventures, retreat to your well appointed cottage reminiscent of Maui plantation style ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Escape to the serene shores of Maui and experience the island life at our charming 2-bedroom, 2-bathroom cottage at the International Colony Club 16, located in the famous Kaanapali Beach area. Embracing the essence of Hawaiian hospitality, our ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Lahaina
Escape to paradise in this remodeled plantation style cottage nestled in the heart of the International Colony Club, Kaanapali, Maui. Boasting 3bedrooms and 2bathrooms, this charming retreat offers ample space for relaxation and rejuvenation. ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Welcome to your dream Hawaiian getaway at our beautifully remodeled plantation cottage in the heart of Kaanapali, Maui. Nestled across the street from breathtaking Kahekili Beach Park, or Airport beach as the locals call it, our air-conditioned ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Lahaina
