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,
(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 = '96734' OR postalCode = '96734' OR region like '%|96734|%') AND sleeps>=1 order by rating desc LIMIT 0, 50
"In order to comply with Oahu's 30 day minimum short term rental laws, we may not advertise to rent for a period shorter than 30 days."Registration Number: 420200410000, TA-071-652-3520-01, GE-071-652-3520-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua
Charming beachfront cottage right on Kailua Beach! Registration Number: TA-096-237-9776-01, GE-096-237-9776-01, 430120130000
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua
*Please read full description! Registration Number: TA-065-131-7248-01, GE-065-131-7248-01
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua
Tropical Trade Winds and Unsurpassed Views are from our Beach Front Home. Opens up for full enjoyment of Hawaii's Climate. Our rap-around Lanai overlooks the beautiful beach, Kailua Bay, and Offshore Islands.Registration Number: TA-191-191-0400-01, GE-191-191-0400-01, 430130460000
4 bedroom
5 bathroom
Sleeps 8
Hawaii> Kailua
Aloha, this unique Premier Partner property is exclusively available for rental for periods of thirty (30) days or more. Questions, please contact us for the dates you are interested in and we will respond wiki-wiki (fast !).Registration Number: 420480020000, TA-120-590-3360-01, GE-120-590-3360-01
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua
Calendar may not reflect accurate availability. Please contact owner with custom requests. 8085547738Registration Number: TA-123-607-8592-01, GE-123-607-8592-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua
Welcome to Walker's Lanikai Beach House. John Walker, was a Scotsman who made his way to Hawaii as a young man in the 1870's to become a contractor of choice to Queen Liliuokalani, and erect such notable buildings as the Bishop Museum and ... Registration Number: 430080930000, TA-043-470-0288-01, GE-043-470-0288-01
5 bedroom
4 bathroom
Sleeps 14
Hawaii> Kailua
Set on a private road with private beach access, Kailua Beachside is One-Story Home a 4BD/4BA (over 2,800 square feet) estate home that has been extensively remodeled and well-cared for. Nestled amongst mature landscaping, the property offers ... Registration Number: 430180560000, TA-196-376-7808-01, GE-196-376-7808-01
4 bedroom
4 bathroom
Sleeps 14
Hawaii> Kailua
Place in Paradise - Kailua offers casual oceanfront accommodations on one of the most beautiful beaches in Hawaii. Kailua Beach has been voted "Best Beach in the United States" by a variety of publications. Five bedrooms and five and half ... Registration Number: GE-060-877-8240-01, 430210250000, TA-060-877-8240-01
5 bedroom
6 bathroom
Sleeps 14
Hawaii> Kailua
With 3 separate living spaces, this remodeled Lanikai home is ideal to accommodate up to 14 guests. The main house of Hale Mokulua - Lanikai consists of 3 bedrooms, 3.5 baths with an open floor plan, high ceilings, hard wood floors, modern ... Registration Number: GE-005-853-7984-01, TA-005-853-7984-01
5 bedroom
6 bathroom
Sleeps 14
Hawaii> Kailua
Contact host for updated calendar. Come and experience life on the lake! This 750 sq ft, second floor one bedroom, one bath with kitchenette, private lanai and private entrance is just what you are looking for. Located in a nice, quiet ... Registration Number: GE-064-829-8496-01, TA-064-829-8496-01, 420610170000
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua
Registration Number: TA-065-681-2032-01
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Kailua
Turn key ready for a relaxing stay. Registration Number: 143024003000, GE-111-409-1520-01, TA-111-409-1520-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua
This airy, spacious four bedroom Kailua beachside vacation rental home with AC provides the essence of tropical Hawaiian living. Remodeled kitchen and bath(s) finish off this stunning home. Whether you prefer lounging in the sun alongside the ... Registration Number: 430190530000, GE-126-659-1744-01, TA-126-659-1744-01
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Kailua
Nestled in the highly sought after wonderful Lanikai community, this beautiful executive home was newly built in 2014. This home is a perfect vantage point.Registration Number: 430050050000, TA-209-884-5696-01, GE-209-884-5696-01
3 bedroom
4 bathroom
Sleeps 8
Hawaii> Kailua
Beachfront! The White Sand of Kailua Beach at Your Door!Registration Number: GE-189-305-8560-01, TA-189-305-8560-01
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Kailua
Come and enjoy our island home with us. Koi Palms Cottage is located within our gated property alongside our lovely Koi pond, in the quaint town of Kailua. The owners live on the property in the main house.Registration Number: 143072028000, GE-058-690-1504-01, TA-058-690-1504-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua
Location Location Location! Our Lanikai ocean front location exactly in front of the twin Mokulua Islands provides endless hours of outstanding views from the ocean front yard, the living room, dining room, and side deck. Registration Number: TA-139-440-7424-01
3 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua
Registration Number: GE-030-384-5376-01, TA-030-384-5376-01
4 bedroom
4 bathroom
Sleeps 7
Hawaii> Kailua
Tropical setting just steps away from beautiful Kailua Beach. Located on a quiet cul-de-sac beachside of Kalaheo Ave. This quiet and private accommodation is ideal for 2 couples or a small family. Aprox 900sq ft of indoor living area, complete ... Registration Number: TA-036-719-8208-01, GE-036-719-8208-01
2 bedroom
2 bathroom
Sleeps 4
Hawaii> kailua
This gorgeous 5 bedroom, 4.5 bathroom, gated oceanfront villa was built in 1918 and has just been fully remodeled with an open, contemporary beach house feel while still honoring the original architecture of the home. In the renovation we ... Registration Number: GE-180-925-6448-01, TA-180-925-6448-01
6 bedroom
5 bathroom
Sleeps 15
Hawaii> Kailua
Only steps to beach through private street gate, and a short 1.5 mile bike to Kailua town. 2 bedrooms, family room, and kitchen on 2nd floor; laundry, 1 bedroom and bath on 1st floor. Washer/Dryer, Basic Cable, and Wifi includded. Small pets ok.... Registration Number: 430180410000, TA-199-189-0944-01, GE-199-189-0944-01
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua
Relax in the beautiful three-bedroom seaside villa in Kailua, where soothing sea waves and soft breezes carry you off. Indulge in the outdoor pool, where you can soak up the sun and relax and end a day of hiking or golf with a massage in the ... Registration Number: 440390160000, GE-097-705-3696-01, TA-097-705-3696-01
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Kailua
Registration Number: 430100170000, TA-065-681-2032-01, GE-065-681-2032-01
3 bedroom
3 bathroom
Sleeps 5
Hawaii> Kailua
Paradise is now more affordable than ever.Registration Number: GE-203-074-9696-01, TA-203-074-9696-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua
Just steps from one of the most beautiful tourist-free beaches on the island of Oahu, this Hawaiian style home is a perfect peaceful getaway for your extended family. Listen to the waves crashing as you relax on the lanai, or barbeque for your ... Registration Number: GE-054-032-3840-01, 430180510000, TA-054-032-3840-01
5 bedroom
3 bathroom
Sleeps 10
Hawaii> Kailua
Hale Kainalu is a sweet getaway at the end of Lanikai with exclusive secluded beach used by only 5 houses within a gated drive. This beautiful cottage is literally right on the beach & overlooks the crystal clear, blue and green hued water. Wake... Registration Number: GE-180-925-6448-01, TA-180-925-6448-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua
TA-006-302-3104-01Registration Number: GE-006-302-3104-01, TA-006-302-3104-01
4 bedroom
3 bathroom
Sleeps 10
Hawaii> Kailua
A comfortable beachfront home set in a security-gated neighborhood with central air condition. Decorated with island style rattan furnishings, ceiling fans throughout the house, cool white tile, granite counters and wooden flooring. There is a ... Registration Number: 430180100003, TA-083-865-6000-01, GE-083-865-6000-01
4 bedroom
3 bathroom
Sleeps 10
Hawaii> Kailua
Aloha! We renovated this old-style Hawaiian home so that it will have the charm of old Hawaii with modern conveniences.Registration Number: GE-077-835-2640-01, TA-077-835-2640-01, 430110790000
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua
Just steps away from coveted Lanikai Beach, sits our newest luxury rental - Lanikai Breeze. Built in 2017, this brand new construction boasts upgraded appliances and furnishings meticulously hand picked by local designer, Lotus Home Staging. ... Registration Number: 430070810000, GE-135-880-0384-01, TA-135-880-0384-01
5 bedroom
5 bathroom
Sleeps 10
Hawaii> Kailua
Welcome all guests...long term or min 30 days stays welcome ...Kailua is a relaxed lifestyle, and a Hawaiian home to relax and vacation with your family and friends.The beauty of Judys' Beachside Corner is that you can have one bedroom ... Registration Number: 143080011000, TA-131-046-6048-01, GE-131-046-6048-01
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Kailua Beach
NAUPAKA HALERegistration Number: TA-151-566-1312-01, GE-151-566-1312-02
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Lanikai
State of Hawaii Transient Accommodation Registration: TA-046-155-5712-01Registration Number: TA-197-713-5104-01, GE-046-155-5712-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua
State of Hawaii Transient Accommodation Registration: TA # 197-713-5104-01Registration Number: TA-197-713-5104-01, GE-046-155-5712-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua
HALE LANIKAIRegistration Number: 430050500000, GE-161-257-7280-01, TA-161-257-7280-01
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Kailua
Aloha! We renovated this old-style Hawaiian home so that it will have the charm of old Hawaii with modern conveniences.Registration Number: GE-077-835-2640-01, TA-077-835-2640-01, 430110790000
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua
Pacheco's Hale is a beautiful two-bedroom, second-floor unit - nice and breezy with a private entrance. We have been renting our property since the 1980s and this unit since 2014. This clean, quiet unit is completely furnished and updated ... Registration Number: 42020066, TA-168-409-2928-01, GE-168-409-2928-01
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua
Charming Lanikai Cottage features two dwellings on the same property, providing a total of three bedrooms and two bathrooms. Although separate, the dwellings share a private fenced-in yard with a new pool. Furnished in a beach-chic style, both ... Registration Number: TA-111-603-0976-01, GE-111-603-0976-01, 430040320000
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua
Indulge in the tranquility of Lanikai Beach, just a stone's throw away, and let the allure of this coastal paradise wash over you. With the studio's inviting comforts and the endless ocean possibilities at your disposal, your stay promises to be... Registration Number: GE-095-917-0560-01, TA-095-917-0560-01, 143005018000
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua
E Komo Mai! (Welcome!)Registration Number: TA-010-529-4336-01, GE-010-529-4336-01
0 bedroom
1 bathroom
Sleeps 3
Hawaii> Kailua
This Kailua, beachside, pool (heated) home was completely remodeled in 2019 with all new kitchen, bathrooms, pool house, pool surround, landscaping, TV's, furnishings, etc. And NO CLEANING FEE on 30 day bookings!Registration Number: GE-174-185-2672-01, TA-174-185-2672-01, 430190450000
5 bedroom
4 bathroom
Sleeps 10
Hawaii> Kailua
ORCHID HOUSERegistration Number: GE-087-311-0016-01, TA-087-311-0016-01
3 bedroom
4 bathroom
Sleeps 7
Hawaii> Kailua
Welcome to the Penthouse in Lanikai! We have been sharing our piece of paradise for over 20 years.! Everyone deserves to experience the Hawaiian lifestyle! Sunrises, moonrises, walks, bike rides, and daily runs on the beach. We have guests from ... Registration Number: 43005029, W00530010-01, GE-136-047-8208-01, TA-136-047-8208-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kailua
Perfect Kailua Home on 18,000 square feet of land all your own!! Lots of room to spread out!! We have a gorgeous newly tiled Swimming Pool and spa! You are invited to rest and relax in “Ka Hale Lai” , Kailua’s House of Serenity, 5 ... Registration Number: TA-027-765-7600-01, GE-027-765-7600-01
5 bedroom
3 bathroom
Sleeps 8
Hawaii> Kailua
Experience luxurious coastal living at Lanikai Seashore, a tropical two-story vacation rental just steps away from the renowned Lanikai Beach. This villa’s spacious open floor plan and unobstructed ocean views makes one feel they’re almost ... Registration Number: 430050620000, TA-151-764-5824-01, GE-151-764-5824-01
4 bedroom
4 bathroom
Sleeps 12
Hawaii> Kailua
Turn key ready.Registration Number: 143024003000, GE-111-409-1520-01, TA-111-409-1520-01
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua
Let the relaxing atmosphere of Lokomaika’i Kailua (meaning Gracious Kailua) whisk you into vacation mode. The home includes 2 primary suites that beckon you to the outdoor areas of the pool and garden. There are also 2 additional bedrooms and ... Registration Number: TA-079-127-1936-01, GE-07
5 bedroom
3 bathroom
Sleeps 11
Hawaii> Kailua
One of the original homes on Kailua Beach, this Renovated 1937 five bedroom Cottage is just one house from the middle of Kailua Beach, the #1 Beach in the world. Nestled between a park and a rarely used club with only one beachfront neighbor, ... Registration Number: GE-045-319-8848-01, TA-045-319-8848-01, 430160220000
5 bedroom
2 bathroom
Sleeps 10
Hawaii> Kailua
ALOHA! The Kailua Bliss Beach House is a casual and relaxed beach house in the quiet Beachside neighborhood in Kailua, located on a private street, 1 house from the white sands of Kailua beach (Private beach gate is located adjacent to our ... Registration Number: TA-068-883-0976-01, GE-068-883-0976-01, 143018061000
5 bedroom
4 bathroom
Sleeps 10
Hawaii> Kailua
