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 '%Kauai Kailani%') AND sleeps>=1 order by rating desc LIMIT 0, 20
This condo has it all. Elevated view of the ocean with the sun rising over the water. The large covered lanai is perfect for outdoor dining/living. The second bedroom contains a Queen size bed. Stacked washer and dryer. Only steps to the ocean ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapa?a
This beautiful air-conditioned home is well maintained and in "like new" condition. You will love being this close to the beach, and relaxing in comfort when not on the go. The kitchen contains all the amenities if you decide to skip Kauai's ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapa?a
Kauai Kailani a Beautiful Garden View! A 2 Bedroom Condo located along the famous Coconut Coast on Beautiful Kauai East Shore!
The Experience: Peaceful in this nicely decorated condo just steps to the beach!
And, there is AC in ...
- 2 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Kapaa
This modern, clean and comfortable air conditioned home features stainless steel appliances, granite countertops, high-end furnishings and a stackable washer and dryer. You will be located in the heart of Kapaa and within walking distance of ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapa?a
This desirable top-floor, ocean view suite is located within the oceanfront Kauai Kailani Resort. Newly remodeled, this suite is a fresh find on Kauai’s Coconut Coast in Kapaa.
Fitted with new, top-quality furniture, kitchenware and ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kapaa
It boasts the comforts of air conditioning, a fully equipped kitchen, washer & dryer and its own reserved parking spot near the elevator for easy access to the second floor.
Enjoy the onsite amenities of two pools, BBQ grills and a rec ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapa'a
Stylish décor, cool tile floors & open design make this 635sf suite pure nirvana. Beautiful views flow from the lanai into the open living area & kitchen perfect for prepping nosh to share “at home.” Touches include upgraded baths, ceiling ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapa'a
Blissful Pacific and pool views can be seen from the lanai of this chic open-design suite! Guests can enjoy all of the amenities of home including air-conditioning, three ceiling fans, one flat-screen television, and a full kitchen to prepare ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapa'a
You may never want to leave the oceanfront lanai of this 864 sq ft Kauai vacation home! Prepare meals in the upgraded kitchen with dishwasher, and then relax with WiFi and a flat screen TV. Other conveniences include a private den and ceiling ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapa'a
