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
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
