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 doubles as a den. Stacked washer and dryer. Only steps to the ocean and ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapaa
PROPERTY DESCRIPTION
You've found a well-located gem on Kauai's Coconut Coast! This Kapaa condo community features an oceanfront pool, beach access, and a grassy lawn for taking in the sea views (including truly spectacular sunrises). While ...
- 2 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Kapaa
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> Kapaa
Stylish décor, cool tile floors and open design make this 635sf suite pure nirvana. Beautiful views flow from the lanai into the open living area and kitchen perfect for prepping nosh to share “at home.” Touches include upgraded baths, ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> KAPAA
Kauai Kailani a Beautiful Garden View! Fully Renovated 2 Bedroom Condo located along the famous Coconut Coast on Beautiful Kauai East Shore!
The Experience: peaceful in this well decorated, fully renovated condo just steps to the beach!...
- 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> Kapaa
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
Kauai Kailani unit #213, located on the second floor: Embrace island living in this condo with pool and ocean views. Chic island décor and attractive furniture make this retreat a favorite for families or friends traveling together. This condo ...
- 2 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> KAPAA
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 6
Hawaii> KAPAA
It boasts the comforts of air conditioning, a fully equipped kitchen, washer and 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 6
Hawaii> KAPAA