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 '%Hanalei Bay Villas%') AND sleeps>=1 order by rating desc LIMIT 0, 30
10 out of 10, Traveler Award winner for 2024. Hanalei Bay Villa 26 is one of our most popular Kauai vacation rentals with its great views, short walk to beach, and updated interior. Bail Hai sunset views are just steps away. This luxurious ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Hanalei Bay Villas #17 is a beautifully remodeled stand-alone condominium. This luxurious bungalow already had panoramic views of Hanalei Bay and the Hanalei Mountains and the interior reflects the beauty of its surroundings. Beautiful ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Princeville
Hanalei Bay Villas #36 is a spacious three bedroom, two and a half bath, two story stand-alone condominium. Beach access is available past the Princeville Hotel next door. Views include the Hanalei Mountains and the open ocean. There's a ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Princeville
Hanalei Bay Villas #22 is a spacious two bedroom, two and a half bath, two story stand-alone condominium. With an additional queen size sleeper sofa, this unit sleeps six comfortably. Beach access is available past the Princeville Hotel next ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Princeville