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 (city = 'Waikoloa Beach Resort' OR postalCode = 'Waikoloa Beach Resort' OR region like '%|Waikoloa Beach Resort|%') AND sleeps>=1 order by rating desc LIMIT 0, 20
This vacation rental is professionally managed by ResorticaHawaii
Waikoloa Beach Villas C23
Please scroll down for detailed information:
PLEASE NOTE: Waikoloa Beach Villas will be restoring exterior woodwork throughout the complex....
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa Beach Resort
This large upscale 2BR/2BA villa is at Hali'i Kai, the only OCEANFRONT condo community within the famous Waikoloa Beach Resort. Besides a great condo, you will get to enjoy Hali'i Kai's unique Ocean Club. Perched at the ocean's edge, it ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Waikoloa Beach Resort
This vacation rental is professionally managed by ResorticaHawaii
Halii Kai 13A. Includes Waikoloa Gold Golf Membership Discounts
Please scroll down for detailed information
Bedding Configuration:
Master Bedroom: King ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Waikoloa Beach Resort
11C is a prime location to enjoy those seamless Hawaiian sunsets. Situated right by the ocean, you will be able to smell the salt air as the winds of Waikoloa breeze on by. The condo is footsteps away from the Ocean Club, where you can enjoy ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Waikoloa Beach Resort
This vacation rental is professionally managed by ResorticaHawaii
Waikoloa Beach Villas I2.
Please scroll down for detailed information
PLEASE NOTE: Waikoloa Beach Villas will be restoring exterior woodwork throughout the complex....
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Waikoloa Beach Resort
Maximum occupancy strictly enforced in accordance with local ordinances.
Remodeled in 2018, this luxurious 3 bedroom villa is ideal for family and friends looking to explore the Hawaiian island. The Waikoloa Beach Resort has numerous ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
Hawaii> Waikoloa Beach Resort
Kolea 14K Current Special Rate $550/nt+tax plus $100 Lava Lava Beach Card for a Limited Time!
The perfect vacation rental awaits you! This 3 bedroom, 2 bath condo is centrally located in the beautiful oceanfront neighborhood of Kolea. ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa Beach Resort
This vacation rental is professionally managed by ResorticaHawaii
Halii Kai 10G. Ocean Views!
Please scroll down for detailed information
Bedding Configuration:
Master Bedroom: King Bed
Guest Bedroom: Queen Bed
Guest ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Waikoloa Beach Resort
This vacation rental is professionally managed by ResorticaHawaii
Waikoloa Beach Villas E23
Please scroll down for detailed information
PLEASE NOTE: Waikoloa Beach Villas will be restoring exterior woodwork throughout the complex....
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Waikoloa Beach Resort
This vacation rental is professionally managed by ResorticaHawaii
Waikoloa Fairway Villas H4
Please scroll down for detailed information
Bedding Configuration:
Master Bedroom: King Bed
Guest Bedroom Downstairs: One Queen ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Waikoloa Beach Resort
This vacation rental is professionally managed by ResorticaHawaii
Waikoloa Colony Villas 1002
Please scroll down for detailed information.
Bedding Configuration:
Master Bedroom: King Bed
Guest Bedroom: King Bed
Guest ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Waikoloa Beach Resort
This vacation rental is professionally managed by ResorticaHawaii
Waikoloa Beach Villas L2
Please scroll down for detailed information
PLEASE NOTE: Waikoloa Beach Villas will be restoring exterior woodwork throughout the complex. ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa Beach Resort
This vacation rental is professionally managed by ResorticaHawaii
Shores at Waikoloa Villa 2
Please scroll down for detailed information
Bedding Configuration:
Master Bedroom: King Bed
Guest Bedroom: 2 Queen ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Waikoloa Beach Resort
This vacation rental is professionally managed by ResorticaHawaii
Fairway Villas at Waikoloa Beach Resort J23
Please scroll down for detailed information
Bedding Configuration:
Main Bedroom: King Bed
Guest Bedroom : 1 ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Waikoloa Beach Resort
PLEASE NOTE: Waikoloa Beach Villas will be restoring exterior woodwork throughout the complex. The scope of work to include Ohia Wood Posts a, 2nd and 3rd floor railings, trellises and A/C enclosures at each building. Each building will be ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Waikoloa Beach Resort
This vacation rental is professionally managed by ResorticaHawaii
Shores at Waikoloa 331
Please sPcroll down for detailed information
Bedding Configuration:
Master Bedroom: King Bed
Living Room: Sleeper ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Waikoloa Beach Resort
This vacation rental is professionally managed by ResorticaHawaii
Waikoloa Beach Villas J2
Please scroll down for detailed information
PLEASE NOTE: Waikoloa Beach Villas will be restoring exterior woodwork throughout the complex. ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Waikoloa Beach Resort
Waikoloa Fairway Villas N24
This vacation rental is professionally managed by ResorticaHawaii
Please scroll down for detailed information
Hilton Pool Pass: Valid for stays in 2024. Maximum 6 persons per pass (children under the ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa Beach Resort
Fairway Villas at Waikoloa E2
Please scroll down for detailed information
This vacation rental is professionally managed by ResorticaHawaii
Bedding Configuration:
Master Bedroom: King Bed
Guest Bedroom : Queen ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa Beach Resort
Waikoloa Colony Villas 101. Newly remodeled in 2023!
Please scroll down for detailed information
Hilton Pool Pass: Valid for stays in 2024. Maximum 6 persons per pass (children under the age of 5 are not counted towards the 6 person ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa Beach Resort
