select * from (Select a.`postalCode`,a.`uniturl`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `region`, `source`, `property_type`, `location_type`, `suitability`, `features`, `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) as vw where 1 and round(price)>0 and bedrooms>=0 AND (`headline` like '%Vista Waikoloa%') AND sleeps>=1 order by rating desc LIMIT 0, 15
The LoveRentals portfolio is comprised of curated, professionally managed vacation rental homes around the world. We only select professional property managers that meet our high standards. Book with peace of mind knowing that you will have the ...
2 bedroom
2 bathroom
Sleeps 4
HI> Waikoloa
Aloha and Welcome to our 2 bedroom 2 bath Ocean View Villa at The Vista Waikoloa. A perfect location for your stay!!
We have central A/C, free secured Wifi, free parking, washer/dryer and beach gear.
Master bedroom has a king bed, flat screen...
2 bedroom
2 bathroom
Sleeps 5
HI> Waikoloa
Vista Waikoloa D302
Please scroll down for detailed information
Bedroom Configuration:
Master Bedroom: King Bed with ensuite bathroom
Guest Bedroom: Queen Bed with ensuite bathroom
Living Room: Twin Fold Out Sofa
Amenities:
Beach ...
2 bedroom
2 bathroom
Sleeps 5
HI> Waikoloa Village
Welcome to this fully renovated 2-bedroom vacation condo at the Vista Waikoloa Beach Resort. Located in the C building, this second floor unit boasts superior views of the ocean and coastline (even from the comfort of your master bed!). This ...
2 bedroom
2 bathroom
Sleeps 4
HI> Waikoloa
Renovated in 2018, this plush 2 bedroom, 2.5 bathroom condo is one of the most modern properties at the Vista Waikoloa. E203 was recently refurbished with gorgeous koa cabinets in the kitchen, re-imagined marble bathrooms and brand new ...
2 bedroom
3 bathroom
Sleeps 6
HI> Waikoloa
*Completely renovated 2 bedroom, 2 bath third-floor condo with ocean views.
*Fully redecorated in 2018 - one of the most modern Vista units!
*Only a short walk away from the beautiful coastline trail and the sands of A-Bay.
* Located on the ...
2 bedroom
2 bathroom
Sleeps 4
HI> Waikoloa
Welcome to the Big Island of Hawai'i, the land of Aloha! In naming our hale “Wai Aloha” we hope to share our love of water that creates this tropical paradise; perfect for relaxing at waialoha.vacations.
Initial effort for screening ...
2 bedroom
2 bathroom
Sleeps 5
HI> Waikoloa
Our slice of Hawaiian Paradise!
Third floor corner unit at Vista Waikoloa. Private lanai with beautiful views of Mauna Kean, Moana Loa and Hualalai mountains plus A Bay beach palm trees and ocean. In the winter months you can see the snow ...
2 bedroom
2 bathroom
Sleeps 4
HI> Waikoloa Village
Vista Waikoloa D101.
Please scroll down for detailed information
****PLEASE NOTE: The Hilton Pool Pass that is mentioned in the reviews from past years, is no longer acitve for this vacation rental
Bedding Configuration:
Master ...
2 bedroom
2 bathroom
Sleeps 6
HI> Waikoloa Village
Our Condominium
Our condo at Vista Waikoloa complex is surrounded by beautiful views of the ocean, lush gardens, palm trees, and fairways of the Beach Golf course in the famous Waikoloa Beach Resort. From the front door you can walk to the ...
2 bedroom
2 bathroom
Sleeps 4
HI> Waikoloa Village
Vista Waikoloa A304. Ocean Views!
Please scroll down for detailed information
***PLEASE NOTE: Due to current Covid 19 protocols there may be modified hours for Pool Services, Fitness Rooms or Community/BBQ Areas ***
Bedroom ...
2 bedroom
2 bathroom
Sleeps 4
HI> Waikoloa Beach Resort
B105 is a newly renovated 2-bedroom, 2.5 bathroom condo in a prime location at the prestigious Waikoloa Resort - only a short walk-away from the coastline and the sandy beaches of A-Bay. Completely upgraded in 2018, the condo features new ...
2 bedroom
3 bathroom
Sleeps 4
HI> Waikoloa
* 2 Bedroom, 2 Bath garden view unit located on the second floor.
* Living room is furnished in rattan pieces and has a sofa-bed and an entertainment center with TV/DVD/VCR and CD stereo system.
* Master bedroom has a king size bed and TV.
* ...
2 bedroom
2 bathroom
Sleeps 6
HI> Waikoloa
Our beautiful 2 bedroom / 2 bath villa has a fully equipped kitchen to your convenience. The oversized flatscreen has cable and a DVD player. The outside Lanai has a sink, a dinner table with 4 chairs and 2 lounge chairs to catch some sun!!! ...
2 bedroom
2 bathroom
Sleeps 5
HI> Waikoloa
* 2 Bedroom, 2.5 Bath garden view unit.
* Comfortably furnished with a TV/DVD/VCR and CD player in the living room.
* Master bedroom has a king bed and television.
* Second bedroom has two twin beds.
* Rollaway in unit to accommodate 5th person....
2 bedroom
3 bathroom
Sleeps 5
HI> Waikoloa
