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 (registration_number like 'TA-%') AND (city = 'waialua' OR postalCode = 'waialua' OR region like '%|waialua|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
This recently renovated private retreat awaits you in this spectacular home where the glass doors slide away to open the interior to the lanai and pool area providing an experience of indoor/outdoor living. This custom, masterful estate offers 7... Registration Number: TA-161-202-8416-01, GE-16
7 bedroom
5 bathroom
Sleeps 18
HI> Waialua
Spacious modern Hawaii Beach Condo located across the street from a tranquil beach and surrounded by mountain views. In a quiet neighborhood away from crowds yet minutes to historic Haleiwa town with shops and restaurants. Quick drive to world ... Registration Number: TA-036-678-7072-01, W4670
1 bedroom
1 bathroom
Sleeps 3
HI> Waialua
Oceanfront Open Land ranch with new 22 ft. Bambi Airstream vehicle w/ table, 4 chairs & BBQ grill. The ranch has horses , farm animals, Native Hawaiian plants on a 26 acre private peninsula & 2,300 ft of beachfront. One panoramic 1-mile ... Registration Number: TA-159-578-1120-01, 10060
1 bedroom
1 bathroom
Sleeps 4
HI> Waialua
Enjoy country style living in North shore of Oahu. The studio is available to rent monthly.
5 minute walk from the beach.
Swim and snorkel year around in Waialua. In the evenings, enjoy a colorful sunset over the ocean.
Detached Studio ... Registration Number: TA-149-777-7152-01, 16700
1 bedroom
1 bathroom
Sleeps 3
HI> Waialua
This beachfront home is located in a quiet family neighborhood. Party animals, please look elsewhere! We are well off the main road with very little traffic making it a great place to take walks or go jogging. We are situated on a long white ... Registration Number: TA-115-667-1488-01, GE-11
4 bedroom
3 bathroom
Sleeps 10
HI> Waialua
TWO MASTER SUITES!! Search no luxurious oceanfront beauty has all the makings of a heaven-sent vacation on one of Oahu's most secluded coastlines. Furnished throughout with hand-picked Balinese furniture and decor, this home is a treat to see ... Registration Number: TA-101-629-1328-01
4 bedroom
4 bathroom
Sleeps 8
HI> Waialua
The Waialua Honu Hale is located on a scenic sandy beach, on a quiet cul-de-sac. A five minute drive into Haleiwa lets you enjoy restaurants, shopping, and adventures. This quaint town is the surfing capitol of the world.
This beachfront home ... Registration Number: TA-022-122-7520-01, GE-02
4 bedroom
2 bathroom
Sleeps 8
HI> Waialua
This is a short description required due to a recent change in the Law. Our unit is located outside of one of the resort areas so a 30 days minimum does apply to this unit.
This beautiful property is located less than 200 feet from the ... Registration Number: TA-206-193-6604-01, GE-20
1 bedroom
1 bathroom
Sleeps 4
HI> Waialua
This gem is located on a white sand beach with sweeping views of the North Shore. The views of the Ocean from the kitchen and living room are amazing and there is a sliding glass door that opens to the Lanai overlooking the beach.
As part of... Registration Number: TA-099-583-5904-01, GE-09
2 bedroom
1 bathroom
Sleeps 4
HI> Waialua
Our Ocean Front Condo has amazing views of the North Shore and miles of white sand beach stretching along the coast. Oahu regulations require a minimum stay of 30 days. This home is also available for longer stays with larger discounts for ... Registration Number: TA-099-583-5904-01, 67013
3 bedroom
3 bathroom
Sleeps 8
HI> Waialua
Ultra High Speed Internet.
*Monthly Special Rate is rent only. Tax/fees extra. only lasts until Nov 31, 2021.
New VRBO Listing! Reviews can be found on our direct website and other platforms. Please contact us with any questions.
- BEACH... Registration Number: TA-090-694-8608-01, 67015
4 bedroom
3 bathroom
Sleeps 10
HI> Waialua
Enjoy a spacious studio by the beach with all amenities and high speed Internet/WiFi included. With just steps to the ocean, soaking up the sun and surf will become an everyday event.
Conveniently located 5 minutes away from Haleiwa town and ... Registration Number: TA-030-847-6416-01, GE-03
1 bedroom
1 bathroom
Sleeps 2
HI> Waialua
