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, 30
This vacation rental is professionally managed by ResorticaHawaii
Waikoloa Beach Villas C23
Please scroll down for detailed information:
Bedding Configuration:
Master Bedroom: King Bed
Guest Bedroom: Queen Bed
Loft ...
- 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:
Bedding Configuration:
Master Bedroom: King Bed
Guest Bedroom: Two Twin ...
- 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
The perfect vacation rental awaits you! This 3 bedroom, 2 bath condo is centrally located in the beautiful oceanfront neighborhood of Kolea. It is one of those versatile homes that provides great space for one or two couples, or the ideal ...
- 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
Hilton Pool Pass: Valid for stays in 2024. Maximum 6 persons per pass (children under the ...
- 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
Bedding Configuration:
Master Bedroom: King Bed
Guest Bedroom: One Queen Bed
Living ...
- 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
This vacation rental is professionally managed by ResorticaHawaii
Waikoloa Beach Villas M23
Please scroll down for detailed information
Bedding Configuration:
Master Bedroom: One King Bed
Guest Bedroom: One Queen Size ...
- 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 Fairway Villas G4
Please scroll down for detailed information
Bedding Configuration:
Master Bedroom: King Bed
Guest Bedroom : Two Full Size ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa Beach Resort
This vacation rental is professionally managed by ResorticaHawaii
Waikoloa Colony Villas 1303
Please scroll down for detailed information
Hilton Pool Pass: Valid for stays in 2024. Maximum 6 persons per pass (children under the ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Waikoloa Beach Resort
This vacation rental is professionally managed by ResorticaHawaii
Waikoloa Beach Villas J2
Please scroll down for detailed information
All new carpet and new appliances installed in 2024!
Hilton Pool Pass: Valid for stays in ...
- 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
This luxurious resort condominium with spectacular views is located along the 12th fairway of the Waikoloa Beach Golf Course and is exclusive only to residents and guests. From the shimmering blue ocean and breathtaking fiery sunsets, to the ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Waikoloa Beach Resort
Maximum occupancy strictly enforced in accordance with local ordinances.
Kolea 14F (1) is nestled along tranquil blue waters and soft sand beaches of Anaeho'omalu Bay (A-BAY). Ideal for families or for individuals who enjoy gracious, ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Waikoloa Beach Resort
This vacation rental is professionally managed by ResorticaHawaii
Waikoloa Fairway Villas K31
Please scroll down for detailed information
Bedding Configuration:
Master Bedroom: King Bed
Guest Bedroom : Two Twin ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Waikoloa Beach Resort
This vacation rental is professionally managed by ResorticaHawaii
Waikoloa Colony Villas 1202. Please scroll down for detailed information
Bedding Configuration:
Master Bedroom: King Bed
Guest Bedroom: Queen Bed
Guest ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Waikoloa Beach Resort
This vacation rental is professionally managed by ResorticaHawaii
PLEASE NOTE: There will be an irrigation project starting April 1, 2024. It is expected to last 3-4 months. The work will be on the outer edge/berm of the East end of ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Waikoloa Beach Resort
This vacation rental is professionally managed by ResorticaHawaii
Shores at Waikoloa 306
Please scroll down for detailed information
Bedding Configuration:
Master Bedroom: King Bed
Guest Bedroom: Queen Bed
Living Room: ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa Beach Resort
This vacation rental is professionally managed by ResorticaHawaii
Waikoloa Beach Villas I22.
Please scroll down for detailed information
Bedding Configuration:
Master Bedroom: King Bed
Guest Bedroom: Queen ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa Beach Resort
This vacation rental is professionally managed by ResorticaHawaii
Waikoloa Beach Villas O32
Please scroll down for detailed information
Bedding Configuration:
Master Bedroom: King Bed
Guest Bedroom: Two Twin ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa Beach Resort
