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 beach resort%') AND sleeps>=1 order by rating desc LIMIT 0, 20
**Please read 'Other Things to Note' in listing** Capture the tranquility and beauty of the Garden Isle from this charming, centrally located condotel unit at the Kauai Beach Resort. From your private lanai, take in Kauai's lush landscape and ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lihue
Enjoy an amazing sunrise from your private patio, looking over beautiful Nukoli'i Beach Park, with a small ocean view. You are just a couple of minutes' walk away from the beach.
Located in building 3 of Kauai Beach Resort and Spa, a ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lihue
Enjoy a delightful beach vacation at Kauai Beach Resort and Spa, a full-service oceanfront resort situated on 25 lush acres along Kauai’s longest strolling beach. This is a privately owned, immaculate ground floor unit steps from the ocean and...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lihue
Premium Double bed unit with outstanding Lagoon & Fountain Views from your ground floor Terrace. Beautiful Gardens too! Enjoy this Amazing Resort created on 25 Beach Front Acres with Waterfalls, Fire Pits, 4 Pools, Waterslide, Spa's, World ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lihue
Our unit is a privately owned hotel room located in the Outrigger Kauai Beach Resort and Spa.
Beautiful second floor unit in building 4 (closest building to the ocean), with an unobstructed view of the ocean. King bed with chaise lounge....
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lihue
A L O H A ! ! !
Top Floor, Pool View, Kids friendly Resort
Private Studio with a King Bed and an Additional Foldable Bed
Sleeps 3: 2 Adults & 1 Child OR Sleeps ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Kauai
Lihue vacation condo in Hanamaulu with kitchen and lanai
Kaua'i Beach Villas D 19, 1-Bedroom, 1-Bath, Ground Floor Unit with Private Lanai off of the Living Room, Walk to Beach.
No resort fees, free Wifi and free Parking!
Enjoy ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lihue
Beautiful remodeled Condo with breathtaking Ocean Views located at Kauai Beach Resort. Centrally located between Kauai’s North and South shores.
The room has 2 queen beds, a spacious bathroom, air conditioning, and a private balcony. ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lihue
Our unit is a privately owned hotel room located in the Outrigger Kauai Beach Resort and Spa.
Beautiful fourth floor unit with King bed. Lovely resort with amazing pools! Beautiful resort and ocean views from the room!!!
The ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lihue
Our unit is a privately owned hotel room located in the Outrigger Kauai Beach Resort and Spa. Beautiful second floor unit with King bed. Lovely resort with amazing pools! The view is of a beautiful serine green field and ocean beyond.
The...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lihue
Lihue vacation condo with kitchen and 2 lanai's
Kauai Beach Villas D 11, 2-Bedroom, 2-Bath, 3rd Floor Unit (no elevator) with Private Lanai off of the Living Room, Walk to Beach.
No resort fees, free Wifi and free Parking!
Enjoy ...
- 2 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Lihue
Our unit is a privately owned hotel room located in the Kauai Beach Resort and Spa.
This is a beautiful second floor unit with a view of the park and ocean behind it. Comfortable king bed with chaise lounge and balcony.
The ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lihue
This is a beautifully furnished room with 2 queen beds and is only a few steps from the beach. This resort is absolutely gorgeous and meticulously well-kept. You will love the lush vegetation, tropical palm trees and emerald green grass ...
- 0 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lihue
This is a lovely third floor unit. It has a Lagoon view, and is priced accordingly. Only steps to the beach! This resort is absolutely gorgeous and meticulously well kept. You will love the lush vegetation, tropical palm trees and emerald green ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lihue
Enjoy a wonderful beach vacation at Kauai Beach Resort and Spa.
A full-service oceanfront resort situated on 25 lush acres along Kauai’s longest strolling beach. This is a privately owned, ground floor unit that is just steps away from the ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lihue
We want to provide you with a complete island experience. Your cozy unit has a private lanai and furnishings drawn from Kauai's vibrant culture and history. Enjoy views together with first-rate amenities such as a sandy bottom oceanfront pool ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lihue
Explore Kauai's beauty and pristine beaches at Lydgate Park, Wailua Falls, and Waimea Canyon—an unforgettable experience for you and your loved ones seeking relaxation and adventure.
Indulge in our oceanfront retreat, where serenity ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Lihue
We want to provide you with a complete island experience. Your cozy unit has a private lanai and furnishings drawn from Kauai's vibrant culture and history. Enjoy views together with first-rate amenities such as a sandy-bottom oceanfront pool ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lihue
We want to provide you with a complete island experience. Your cozy unit has a private lanai and furnishings drawn from Kauai's vibrant culture and history. Enjoy views together with first-rate amenities such as a sandy-bottom oceanfront pool ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lihue