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 '%Lawai Beach Resort%') AND sleeps>=1 order by rating desc LIMIT 0, 30
Lawai Beach Resort 1 Bedroom Sleeps 4
***PLEASE DO NOT PUT IN A RESERVATION REQUEST USING THE “REQUEST TO BOOK” FEATURE***
Book our Amazing Luxury Villa & let The Timeshare Brokers, LLC make the booking process ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Koloa
Enjoy spectacular views from sunrise to sunset from the lanai of your oceanfront Kauai condo. Lawai Beach Resort overlooks one of the best snorkeling and surfing beaches on Kauai, making it a favorite vacation spot for everyone. There are three ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Koloa
**As the resort is reopening, not all amenities are available - only the pools** The resort is only accepting guests who do not require quarantine.
•On Kauai’s sunny South Shore across the street from one of the island’s best ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
Lawai Beach Resort ; 1 Bedroom Suite
....
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Koloa
Concierge Realty is proud to present luxury beach stays at Lawai Beach Resort by Sundance!
Reservations are available in weekly (7 night) increments with arrivals and departures on Thursdays, Fridays, Saturdays, Sundays and Mondays ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Koloa
Concierge Realty is proud to present luxury beach stays at Lawai Beach Resort by Sundance!
Reservations are available in weekly (7 night) increments with arrivals and departures on Fridays, Saturdays, and Sundays Only.
In today's ...
- 2 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Koloa