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, 20
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
•On Kauai’s sunny South Shore across the street from one of the island’s best snorkeling spots
• Near great dining, the famous Poipu Beach Park, Spouting Horn, Allerton Gardens, Maha’ulepu, and the brand new Kukui’ula Shopping ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
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
Summary:
Welcome to our spacious 1-bedroom residence boasting a tranquil garden, a refreshing pool, and picturesque lanais offering partial views of the mesmerizing ocean.
The Space:
The bedroom is equipped with a luxurious ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Koloa
