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,
(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 (registration_number like '%TA-%' or registration_number like '%TA %') AND (city = 'waialua' OR postalCode = 'waialua' OR region like '%|waialua|%') AND sleeps>=1 order by rating desc LIMIT 0, 50
30 day rentals availableRegistration Number: 670140250000, TA-103-545-7024-01, GE-103-545-7024-01
5 bedroom
4 bathroom
Sleeps 8
Hawaii> Waialua
Greetings! Welcome to the - HaleAlohaEstate - on Oahu's North Shore. Come experience a truly authentic Hawaiian experience in our beautiful Estate home with 6+ bedrooms, and 4.5 bathrooms on the quiet – best part of Oahu’s famous North Registration Number: TA-019-002-5728-02, GE-019-002-5728-01, 680130090000
6 bedroom
5 bathroom
Sleeps 20
Hawaii> Waialua
Serene beachfront estate located in a quiet cul-de-sac on the beautiful North Shore of Oahu. Three bedrooms, two bathrooms inside main home and one half bath and shower outdoors. Kitchen is modern and kitchen table looks out onto a Registration Number: 680090110000, GE-026-583-1424-01, TA-026-583-1424-01
3 bedroom
3 bathroom
Sleeps 6
Hawaii> 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: 1680060040001, TA-115-667-1488-01, GE-115-667-1488-01
5 bedroom
2 bathroom
Sleeps 10
Hawaii> Waialua
Hale Nanea is tucked away with a quiet secluded beach in front. The peaceful gardens around the house and white sand beach just steps away allows you to enjoy your own piece of island paradise. It is located on the North Shore of Oahu near Registration Number: TA-174-270-8736-01, 680050460000, GE-174-270-8736-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Waialua
Waialua Retreat is your home away from home! Located on a quiet residential cul-de-sac, you will truly relax as you dine on the lanai of this spectacular, architecturally designed beachfront home, while taking in amazing sunrises and sunsets. Registration Number: GE-096-939-2128-01, 670140290000, TA-096-939-2128-01
4 bedroom
3 bathroom
Sleeps 7
Hawaii> Waialua
Leimoku is a large and accommodating, beautifully updated, and modern Hawaii home on Oahu's famous North Shore. Located on the "quiet side" of the North Shore, away from all the crowds, this 3000 sq.ft. home is comfortable and luxurious, with Registration Number: GE-117-207-9616-01, TA-117-207-9616-01, 680120400000
5 bedroom
5 bathroom
Sleeps 10
Hawaii> Waialua
Featuring Two Master Suites!!!Registration Number: TA-101-629-1328-01, GE-101-629-1328-01, 680040320000
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Waialua
10/3/24 DISCLOSURE: Home is listed for sale and guests will need to accommodate showings with 48 hours notice. Reservations may not be guaranteed if home sells or is in escrow prior to arrival.Registration Number: 680050010000, GE-090-940-0576-01, TA-090-940-0576-01
4 bedroom
3 bathroom
Sleeps 6
Hawaii> Waialua
Experience Hawaii in its purest form in this beautifully built custom home, sitting on an exclusive beachfront property on the North Shore of Oahu. Built with the idea of taking in the gorgeous ocean and mountain views, and maximizing the flow Registration Number: TA-170-545-7664-01, 680050020000, GE-170-545-7664-01
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Waialua
**Please contact for availability!Registration Number: TA-100-695-4496-02, GE-100-695-4496-02, 680040310000
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waialua
This property is offered as a monthly rental. Although full-month occupancy is not required, the monthly rate remains fixed and applies in full, regardless of the number of nights stayed, as prorated rates are not available.Registration Number: GE-207-262-3616-01, TA-207-262-3616-01, 680100210000
4 bedroom
3 bathroom
Sleeps 9
Hawaii> Waialua
The Waialua Beach House is located on the North Shore of Oahu, just steps from the beach. It’s only 2 miles from Haleiwa and its shops and restaurants and a short drive to the famous surf spots of Sunset, Pipeline, Haleiwa and Waimea. This Registration Number: GE-118-066-9840-01, TA-118-066-9840-01, 670150360000
4 bedroom
3 bathroom
Sleeps 10
Hawaii> Waialua
Mokuleia Beach Colony is a perfect country base or north shore retreat in a manicured tropical gated community! 200 yards from the ocean. Easy private beach access, outdoor shower, heated pool, tennis courts, one reserved parking stall and Registration Number: TA-115-303-0144-01, GE-115-303-0144-02, 119252684801
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Waialua
New Listing: Escape to paradise in a 4-bedroom beach front house with all around ocean views. Just steps from the sand, this home features a large kitchen and open-concept living room perfect for group gatherings. Enjoy relaxing ocean sounds Registration Number: 809923212137, TA-422-506-2745-50, GE-834-219-5763-04
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Waialua
***This property is a legally established dwelling that only accepts one reservation in a period of 30 consecutive days or more. Guests are NOT expected or required to remain in residence for the full 30 days (calendar will be blocked for 30 Registration Number: TA-050-720-9728-02, GE-050-720-9728-02, 67014004
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Waialua
If you are looking for a private, romantic location this is it!Registration Number: TA-070-727-4752-01, GE-070-727-4752-01, 680090010019
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Waialua
Aloha! Charming beachy 2 bedroom, one bathroom cottage on Oahus famous North Shore. All new stainless steel appliances, fresh fluffy towels and amazing sheets. WiFi, internet, cable, and AC in bedrooms. Registration Number: 1670100300000, GE-150-327-1424-01, TA-150-327-1424-01
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Waialua
You've found Hawaii’s Best Kept Secret! (And YES WE HAVE A LICENSE TO RENT SHORT-TERM! SEE BELOW) The cottage is adjacent to a whole mile of deserted beaches, where you’ll discover isolated sandy coves, driftwood and shells, and forested Registration Number: GE-142-964-5512-01, 680090010021, TA-142-964-5312-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Waialua
Aloha! Welcome to the - Mokuleia Beach House - on the North Shore of Oahu, Hawaii. The Mokuleia Beach House is a family owned beach house situated in a private section of Mokuleia. Approximately 30 minutes from Honolulu and Honolulu Registration Number: 650080370000, GE-201-182-2592-01, TA-201-182-2592-01
6 bedroom
4 bathroom
Sleeps 16
Hawaii> Waialua
This fully equipped, ocean front, condo unit is located in Waialua on the beautiful north shore of Oahu. The minimum stay is 30 days at a rate of $185 per day or $5,550.Registration Number: GE-165-464-6784-01, 680110460019, TA-165-464-6784-01
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waialua
Kai aniani or Sea of Glass is an architecturally stunning beachfront home on the North Shore of Oahu, completely remodeled in 2018. The A-frame design is accentuated by windows that face Mount Ka’ala, the tallest mountain peak on the island Registration Number: TA-103-545-7024-01, GE-103-545-7024-01
5 bedroom
5 bathroom
Sleeps 10
Hawaii> Waialua
This beautiful studio apartment with stunning mountain views is a perfect place to unwind for your North Shore get-away. The studio features a queen bed with additional twin bed, and it sleeps up to 3 people. There is air conditioning which you Registration Number: GE-173-135-0528-02, TA-173-135-0528-01, 1680130370000
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Waialua
Permit # 90/tvu-0631 Registration Number: GE-005-940-2240-02, 680090010025, TA-005-940-2240-01, Permit # 90/tvu-0631
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Waialua
30 day minimum! Welcome to north shore living! This comfortable, vibrant beach house upstairs unit has everything you need plus more for an amazing vacation. Enjoy ice cold AC after a long day at the beach, a fun, private lanai with a hammock Registration Number: 66032097, TA-088-293-2224-01, GE-088-293-2224-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Waialua
An island jewel on the North Shore of Oahu, this cottage is situated right on the water in a quiet, serene, and peaceful setting. Surrounded by palms and gentle trade winds, the cottage is located in a gated, old-style Hawaiian setting, offering Registration Number: 66032097, TA-088-293-2224-01, GE-088-293-2224-01, #90/TVU-0237
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Waialua
Enjoy this North Shore 1 bedroom, 1 bathroom apartment in the beautiful Hawaiian countryside with stunning mountain views. It's a perfect place to unwind for your North Shore get-away. There is a Queen bed in the bedroom, and the living room Registration Number: GE-173-135-0528-02, TA-173-135-0528-01, 1680130370000
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Waialua
- THE SAND DOLLAR COTTAGE - Registration Number: GE-134-370-2016-01, 680090010026, TA-134-370-2016-01, 90/TVU-0632
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Waialua
Vacation beach home for sun and fun. Registration Number: TA-115-540-5824-01, GE-115-540-5824-03
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Waialua
This is the ultimate getaway for Hawaii visitors seeking a quiet oceanfront setting to relax and forget the hassles of life. Newly remodeledRegistration Number: TA-114-157-9776-01, GE-114-157-9776-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waialua
Tropical North Shore Island Nature Retreat! 2.5 blocks to the beach & famous surf breaks. Close to historic Haleiwa town & many other gorgeous North Shore beaches & world-class surfing!Registration Number: GE-173-135-0528-02, TA-173-135-0528-01, 1570010130044
4 bedroom
1 bathroom
Sleeps 11
Hawaii> Waialua
This property is offered as a monthly rental. Although full-month occupancy is not required, the monthly rate remains fixed and applies in full, regardless of the number of nights stayed, as prorated rates are not available.Registration Number: 670150630000, TA-179-607-3472-01, GE-179-607-3472-01
5 bedroom
5 bathroom
Sleeps 9
Hawaii> Waialua
This property is offered as a monthly rental. Although full-month occupancy is not required, the monthly rate remains fixed and applies in full, regardless of the number of nights stayed, as prorated rates are not available.Registration Number: 670150630000, GE-179-607-3472-01, TA-179-607-3472-01
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Waialua
This property is offered as a monthly rental. Although full-month occupancy is not required, the monthly rate remains fixed and applies in full, regardless of the number of nights stayed, as prorated rates are not available.Registration Number: 670150630000, GE-179-607-3472-01, TA-179-607-3472-01
9 bedroom
6 bathroom
Sleeps 14
Hawaii> Waialua
Minimum 30-day consecutive rental required. NEW Listing. Please inquire for more details. The private estate sits on two large acres of lush tropical land. It is fenced and gated for security. This is indoor, outdoor living at its best. Registration Number: GE-086-083-2256-01, 16813260, TA-086-083-2256-01
7 bedroom
5 bathroom
Sleeps 15
Hawaii> Waialua
**This property may not be rented for less than 30 consecutive days. Rental prices will not be reduced or adjusted based on the number of days the rental is actually used or occupied**Registration Number: 1680110460023, TA-031-084-6976-01, GE-031-084-6976-01
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Waialua
Escape to this oceanfront condo on Oahu’s North Shore. This spacious end-unit features 2 bedrooms, 2 bathrooms, and comfortably sleeps 4+ with 3 beds. Enjoy breathtaking ocean views from large bay windows or relax with the soothing sound of Registration Number: 680110460024, TA-166-031-1040-01, GE-166-031-1040-01
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waialua
Escape to this Tropical North Shore Island Nature Rental Retreat! 2.5 blocks to the beach & famous surf breaks. Rental is a 2-Story Yurt with a generous 1500+ square foot open-concept floor plan. Sleeps up to 11 people. Features kitchen & full Registration Number: GE-173-135-0528-02, TA-173-135-0528-01, nuc 90 tvu 0623, 1570010290126
4 bedroom
1 bathroom
Sleeps 11
Hawaii> Waialua
Your Waialua adventure begins at this fantastic 1-bedroom apartment. The space features a queen bed, AC, a washing machine, and WiFi. The bathroom includes a shower and a hair dryer for your convenience. Nestled in a serene setting, this Registration Number: 168000457389, TA-764-264-5689-01, GE-154-455-4554-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Waialua
