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,
rewritten_description, custom_url, ai_description,
(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 Village' OR postalCode = 'Waikoloa Village' OR region like '%|Waikoloa Village|%') AND sleeps>=1 AND (
(postalCode NOT IN('96701','96706','96707','96712','6717','96730','96731','96734','96744','96782','96786','96789','96792','96826','96795','96797','96813','96814','96815','96816','96817','96818','96819','96821','96822','96825')
AND city NOT IN('Aiea','Ewa Beach','Haleiwa','Hauula','Hawaii Kai','Honolulu','Kaaawa','Kahala','Kahuku','Kailua','Kaneohe','Kapolei','Laie','Manoa','Makaha','Mililani','Nanakuli','Pearl City','Wahiawa','Waialua','Waianae','Waikiki','Waimanalo','Waipahu','Lanikai','Maili')
) OR (registration_number like '%TA-%' or registration_number like '%TA %')
) order by rating desc LIMIT 150, 50
Experience the charm of Koa Dream at Waikoloa Village, a cozy retreat nestled in a lively community known for its stunning views and tropical breezes. This inviting 2-bedroom, 2-bathroom condo offers a perfect blend of resort luxury and residential comfort, making it ideal for families or couples seeking a relaxing Hawaiian escape.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Welcome to Shores Waikoloa, a secure gated community nestled amidst dramatic lava fields within the expansive Waikoloa Resort. This beautifully remodeled one-bedroom unit with new central air conditioning offers the ideal setting for your Hawaiian vacation, combining comfort, convenience, and stunning natural surroundings.
1 bedroom
2 bathroom
Sleeps 2
Hawaii> Waikoloa Village
Welcome to Waikoloa Fairway Villas D4, a beautifully appointed condo located on the first floor of the D Building within the gated Waikoloa Fairway Villas community on the Kohala Coast of Hawaii. This spacious retreat offers a perfect blend of comfort, convenience, and luxury, making it ideal for families, friends, or couples seeking a relaxing getaway on the Big Island.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Aloha Shores, brought to you by Paradise in Hawaii Luxury Homes, is a stunning single-level residence located on the beautiful Kohala Coast. This spacious 4-bedroom, 3-bathroom home offers an upscale, relaxed atmosphere perfect for families or groups seeking a luxurious Hawaiian getaway.
5 bedroom
3 bathroom
Sleeps 11
Hawaii> Waikoloa Village
Discover this spacious and modern corner villa nestled in a serene location with stunning views of the golf course and mountains. The multi-level layout ensures privacy, with no units above or below, providing a quiet retreat. Located just outside the Kings and Queens Marketplaces, it's ideal for shopping, dining, and entertainment, all within minutes.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Escape to Greens at Waikoloa J101, a charming condo nestled in the heart of Hawaii's lush landscape. This inviting retreat offers stunning views of native flora and fauna, providing a perfect backdrop for your vacation. The spacious living area is filled with tropical patterns and vibrant artwork, creating a cheerful and relaxing atmosphere.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Experience the relaxed charm of Waikoloa Village in this lovely one-bedroom condo situated in the gated Fairway Terrace complex. At an elevation of 800 feet, the area benefits from refreshing breezes and slightly cooler temperatures compared to sea level, making it an ideal retreat.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Escape to this tranquil Hawaii vacation home nestled on a picturesque golf course offering breathtaking ocean views and distant Maui sights. Unwind on your private lanai as vibrant sunsets paint the sky each evening. The property features a lovely pool and tennis courts available for guest enjoyment, perfect for relaxing or staying active during your stay.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Welcome to Waikoloa Beach Villa D23, your ideal vacation retreat in the beautiful Waikoloa Beach Resort. This charming two-story villa offers comfort and convenience, perfect for families or groups seeking a relaxing getaway. The villa features a spacious loft that provides an extra bedroom, comfortably accommodating up to six guests.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
This beautifully renovated condo is nestled in a quiet part of the Waikoloa Villas complex, offering a perfect retreat on the Kohala Coast. With 3 bedrooms and 3 bathrooms, it combines comfort and modern style with bamboo flooring, updated bathrooms, and a stunning kitchen.
4 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Escape to paradise at Fairway Villas I-21, a beautifully appointed 2-bedroom, 2-bathroom condo nestled on the 4th fairway of Waikoloa Beach Resort's Lake Nine Golf Course. This tropical retreat offers breathtaking views of the lush golf course and tranquil lake, perfect for relaxation and adventure alike.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Escape to Hau'Oli at Waikoloa Hills #902, a beautifully maintained condo nestled in the heart of Waikoloa Village. This tropical retreat offers a perfect blend of comfort and convenience, making it an ideal choice for your Hawaiian vacation.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Welcome to your perfect Hawaiian getaway! This beautifully updated one-bedroom condo located in the gated Fairway Terrace complex offers a relaxing retreat just minutes from Waikoloa's stunning beaches. Situated on the upper floor of I-208, the condo features a private lanai where you can enjoy your morning coffee or evening cocktail while soaking in the island breezes.
2 bedroom
1 bathroom
Sleeps 3
Hawaii> Waikoloa Village
Experience a tranquil getaway in this charming one-bedroom condo on the Big Island. The spacious unit comfortably sleeps five, featuring beautiful wood floors, 9-foot ceilings with fans, and a private lanai overlooking the golf course and ocean.
1 bedroom
1 bathroom
Sleeps 5
Hawaii> Waikoloa Village
Welcome to Waikoloa Villas E102, a beautifully furnished ground-floor condo perfect for your Hawaiian vacation. This spacious 2-bedroom, 2-bathroom unit offers a comfortable and inviting atmosphere with wood flooring throughout the main living areas and tile in the kitchen and bathrooms.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Welcome to our beautifully upgraded 2-bedroom, 2-bath villa nestled in the heart of Fairway Villas at Waikoloa Beach Resort. This fully equipped retreat offers modern comforts and a prime location for an unforgettable Hawaiian getaway. The recent renovations include new furniture and carpeting, providing a fresh and inviting atmosphere.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Experience the comfort of this charming ground floor condo in Waikoloa Village, offering a peaceful retreat with easy access to amenities and stunning island sights. The condo features two bedrooms and two bathrooms, with a master bedroom that includes a king-size bed and an attached bathroom, while the guest room has two twin beds.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Embark on a memorable trip to the Big Island by staying in this vibrant and cozy 2-bedroom, 2-bath condo located in Waikoloa Village. Surrounded by stunning mountains and close to some of Hawaii's most beautiful beaches, this rental offers an ideal base for small families or groups seeking adventure and relaxation.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Experience the ultimate Hawaii getaway at Halii Kai 22H, a beautifully maintained oceanfront condo located within the exclusive Waikoloa Beach Resort. This three-bedroom unit offers stunning ocean views and a host of amenities designed for comfort and convenience.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Kohala Suites by Hilton Grand Vacation offers a perfect island retreat with easy access to Waiulua Bay. This property features comfortable condo-style accommodations with all the amenities needed for a relaxing stay. Guests can enjoy the outdoor pool and lounge area, complete with a bar and plenty of seating, ideal for unwinding after a day at the beach.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Experience the beauty of Hawaii from Waikoloa Villas H203, a stylish two-story condo offering stunning ocean and golf course views from the covered balcony. The spacious interior features a fully equipped kitchen with stainless steel appliances, ample countertops, and all necessary utensils for preparing meals from your morning coffee to gourmet dinners.
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Waikoloa Village
Welcome to this charming 2-bedroom, 2-bathroom condo nestled in the tranquil Waikoloa Hills at 1000 ft elevation, offering cooler temperatures and refreshing breezes. , this residence provides both comfort and convenience for your Big Island getaway.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
This charming, fully furnished 2-bedroom, 2-bath condo is nestled in Waikoloa Village, surrounded on three sides by the scenic Waikoloa golf course. The location is ideal for those seeking a peaceful retreat close to nature and local amenities.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Discover the perfect Hawaiian getaway at this inviting Waikoloa Village retreat, where stunning ocean vistas and modern comforts blend seamlessly. The spacious open living area with lofty ceilings creates a breezy, relaxing atmosphere, ideal for unwinding after a day of exploring.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Experience the comfort of this fully furnished ground-floor condo located right on the scenic Waikoloa Village Golf Course. Featuring two bedrooms and two bathrooms, this 1,100 sq ft unit offers a relaxing and convenient stay in the heart of the Kohala Coast.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Experience a relaxing Hawaiian escape at Fairway Terrace G101, a charming condo ideally located adjacent to a scenic golf course. This well-appointed unit offers a welcoming atmosphere with modern amenities and comfortable furnishings, perfect for unwinding after a day of adventure.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Waikoloa Village
The Hawaiian spirit of 'aloha' runs deep in our beautifully renovated condo in Waikoloa Village, Big Island. With a natural warmth and welcoming vibe, our property offers affordable comfort in a peaceful, scenic setting. The condo features a spacious 1100 sq ft layout, ideal for couples, families, or two couples sharing.
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Waikoloa Village
Welcome to your peaceful retreat in Waikoloa Village! This charming ground floor 2-bedroom, 2-bath condo offers a comfortable and stylish space for your Hawaiian vacation. Each bedroom features a plush queen-size bed, an attached bathroom, and a flat-screen TV for your entertainment.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Welcome to Greens At Waikoloa L101, a charming condo perfect for your Hawaii vacation. Our real-time updated calendar and accurate nightly rates make planning easy. '
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Waikoloa Village
Discover the perfect blend of comfort and convenience at Waikoloa Fairway Villas N-5, a charming condo nestled in the sought-after Waikoloa Beach Resort. Whether you're here for adventure or relaxation, this well-appointed vacation home offers everything you need for an unforgettable stay.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
This ground-floor, 2-bedroom, 2-bathroom condo is located in Waikoloa Village and is a slice of heaven in Hawaii! It is furnished and offers all the comforts of home, including a fully-equipped kitchen with marble countertops, modern cabinets, and new appliances.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
This charming and recently renovated condo in Waikoloa Village offers breathtaking ocean views and a serene atmosphere. Perfectly situated on the Trent Jones golf course, the unit features a spacious bedroom with a queen bed and a comfortable living area with sliding lanai doors that seamlessly extend the living space outdoors.
2 bedroom
1 bathroom
Sleeps 2
Hawaii> Waikoloa Village
Welcome to E-24 in the Fairway Villas at Waikoloa Beach Resort, a stunning 2-bedroom, 2-bathroom villa perfect for your Hawaiian getaway. The Fairway Villas are ideally situated within the resort, offering picturesque golf course and lagoon views.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Experience the perfect blend of relaxation and adventure at Waikoloa Colony Villas 2401. This bright, airy two-story condo offers picturesque views of manicured gardens and rustling palms from two shaded lanais, providing an ideal setting for both unwinding and exploring.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Discover the stunning Blue Horizon Unit E107, a beautifully furnished ocean-view condo overlooking the Waikoloa Village Golf Course. This thoughtfully upgraded vacation rental offers spacious living areas with partial air-conditioning, ensuring a comfortable stay.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Waikoloa Village
Welcome to your cozy Waikoloa getaway! This charming first-floor suite offers a private bedroom, bathroom, and kitchenette, making it perfect for travelers seeking comfort and convenience. Located in a friendly home where we live upstairs with our family and our well-behaved dog, this space provides a warm, welcoming atmosphere.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Waikoloa Village
Welcome to Waikoloa Fairways C120, a beautifully located condo offering stunning views of two golf course fairways from the spacious lanai. This peaceful retreat is ideal for those seeking a relaxing Hawaiian escape with easy access to top attractions.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Discover this recently purchased family-friendly townhome in Waikoloa Village, Hawaii. Designed with comfort and convenience in mind, it features all-new bedding and supplies, including playful floaties like a spaceship and turtle, making it ideal for kids and families alike.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Experience a spacious and comfortable 2-bedroom Plus suite at Kohala Suites by Hilton Grand Vacations, offering stunning golf course views and a host of amenities for a memorable escape.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Experience a luxurious stay in our spacious 2-bedroom, 2-bathroom suite offering stunning golf course views and modern amenities. Located just a mile from Hilton Waikoloa Village, guests enjoy complimentary resort access, including pools, golf courses, and cultural activities.
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Set within the luxurious Hilton Waikoloa Village Resort on the stunning Kohala Coast, this Ocean View Studio Penthouse offers a perfect blend of comfort and scenic beauty. The 438 sq. ft. suite accommodates two guests and features a plush king-size bed, a private balcony overlooking the ocean, and modern amenities to enhance your stay.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Waikoloa Village
Experience the perfect Hawaiian getaway in this spacious and beautifully maintained two-bedroom townhouse nestled within the highly sought-after Waikoloa Beach Resort community. This inviting condo offers breathtaking mountain and expansive golf course vistas, creating a serene backdrop for your stay.
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Set within the Hilton Waikoloa Village Resort, the Ocean Tower hotel offers a prime location at Waiulua Bay, providing easy access to resort amenities like three pools, a four-acre ocean-fed lagoon, on-site shopping, a full-service spa, and Dolphin Quest.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Waikoloa Village
Gather, relax, and explore at Makaha Villa, a beautifully renovated townhome nestled on the sunny Kohala Coast of Hawaii's Big Island. Located within Waikoloa Colony Villas, this private, gated community offers a peaceful retreat with no upstairs or downstairs neighbors, perfect for families and friends seeking comfort and privacy.
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Summary:
10 bedroom
1 bathroom
Sleeps 30
Hawaii> Waikoloa Village
Welcome to Hale Hau'oli, a beautifully renovated 2-bedroom, 2-bath condo nestled on the western slope of Mauna Kea in Waikoloa Village. Sitting directly on the Robert Trent Jones golf course, our unit offers stunning views of lush Kiawe trees and the vibrant golf activity from the lanai, perfect for morning coffee or relaxing evenings.
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa Village
Experience a relaxing getaway in this beautifully renovated lakeview villa that offers breathtaking sunsets every evening. Located just a short walk from the lake, golf courses, shopping centers, coffee shops, and the beach, this property combines convenience with comfort.
5 bedroom
3 bathroom
Sleeps 8
Hawaii> Waikoloa Village
Immerse yourself in tropical elegance at this beautiful Waikoloa Beach Resort condo, offering two bedrooms, two bathrooms, and a spacious lanai to enjoy stunning sunrises and sunsets. Located within the original community of Waikoloa Beach Resort, this retreat combines modern comfort with Hawaiian charm.
3 bedroom
2 bathroom
Sleeps 7
Hawaii> Waikoloa Village
Looking for the ultimate family escape in Hawaii? Kings Land by Hilton Grand Vacations offers a perfect blend of relaxation, adventure, and luxury on the stunning Big Island. Nestled along the picturesque Kohala Coast, this resort provides an ideal setting for memorable family holidays.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Escape to this delightful resort retreat in Waikoloa, situated just 20 minutes from Kona International Airport on the Big Island of Hawaii. This cozy one-bedroom suite offers a comfortable king bed and a queen-sized sofa bed, perfect for a relaxing getaway.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Waikoloa Village
