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 = '96734' OR postalCode = '96734' OR region like '%|96734|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Beachfront! The White Sand of Kailua Beach at Your Door!
This 4 bedroom, 4 bath, en suite home is the Perfect Tropical Haven.
Tucked away and private, this home embodies the true island lifestyle.
Walk or jog the long stretch of beach or ...
Registration Number: TA-189-305-8560-01, GE-18
4 bedroom
4 bathroom
Sleeps 8
HI> Kailua
Quaint Kailua…Entire 2 bedroom guesthouse on 2nd story (no home below) with gorgeous mountain and lake views. Minutes to everything Kailua has to offer. Near 4 of the country’s best beaches, 5 stunning golf courses, hiking, water sports, ...
Registration Number: TA-129-625-2928-01, 42083
2 bedroom
1 bathroom
Sleeps 4
HI> Kailua
Beautiful attached guest suite. This Private guest suite has a full kitchen, washer/dryer, stainless steel appliances and a walk in glass shower. It has its own separate private entrance. There is also plenty of street parking. The suite is a ...
Registration Number: TA-195-394-1594-02, GE-19
1 bedroom
1 bathroom
Sleeps 3
HI> Kailua
"Kai Lua" means Cave in the ocean in Hawaiian language, Polynesian hide their most valuable treasure into the cave under the water, the only way to find it is to diving down into the crystal water and know exactly where you hide these gems, this...
Registration Number: TA-187-952-4532-01, 14301
3 bedroom
2 bathroom
Sleeps 25
HI> Kailua
(30) FIVE STAR GOOGLE REVIEWS AND DAILY CONTINENTAL BREAKFAST PROVIDED. Our home has 2 fully licensed, SEPARATE B & B suites, each is 1 bedroom and 1 bath (walk in shower) & accommodates up to 2 people. Both have their own private entrance, ...
Registration Number: TA-059-777-1264-01, GE-05
0 bedroom
1 bathroom
Sleeps 2
HI> Kailua
This cute little beach house is just seconds away from famous Kailua beach! Take a plunge into the ocean, or a short walk through the beach park and trail along right on into Lanikai. This beach house is all about Location! You can see the beach...
Registration Number: TA-094-358-3232-01, GE-09
3 bedroom
1 bathroom
Sleeps 6
HI> Kailua
Extra large studio apartment sits right on Enchanted Lake and offers gorgeous views with wetland habitats. Relax to the max! Minutes to everything Kailua has to offer. Near 4 of the country's best beaches, 5 stunning golf courses, hiking, ...
Registration Number: TA-129-625-2928-01, 42083
1 bedroom
1 bathroom
Sleeps 2
HI> Kailua
Light and airy one room cottage with queen sized bed, bath, kitchenette, out door sitting area by the pool.
The sun provides for 70 % electricity used in this eco friendly Cottage.
The lanai overlooks the tropical garden and the salt water ...
Registration Number: TA-136-919-6544-01
1 bedroom
1 bathroom
Sleeps 2
Hi> Kailua
Hawaii GET # GE-197-713-5104-01 and TAT Tax ID# TA-197-713-5104-01
Aloha! We are in compliance with Honolulu City and County Ordinance 18-19 (Bill 89), we welcome 30 day renters. Stays less than 30 days are also welcome, if they are the only ...
Registration Number: TA-197-713-5104-01, GE-19
1 bedroom
1 bathroom
Sleeps 4
HI> Kailua
Take advantage of our vacation rental in Kailua Oahu, and enjoy a family style atmosphere where you are provided with all of the comforts of home.
Whether it be barbecuing under the covered lanai, or taking a walk to our award winning beaches, ...
Registration Number: TA-065-136-8448-01
2 bedroom
1 bathroom
Sleeps 5
Hi> 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.
It boasts 3 bedrooms en suite that are generous in size and more than ample in comfort....
Registration Number: TA-209-884-5696-01, GE-20
3 bedroom
4 bathroom
Sleeps 8
HI> Kailua
Minimum Stay: 2 night
Max Night Stay: 8 nights
Our Hawaiian Style Vacation Rental apartment is located on Kailua Beach, one of Hawaii's most beautiful of white sand beaches. Kailua Beach has been rated tops among USA's best beaches.
The ...
Registration Number: TA-168-049-0496-01, 43018
1 bedroom
1 bathroom
Sleeps 2
HI> Kailua
TAT: TA-181-128-3968-01
Amazing Mountain Views in a Lush and Peaceful Setting
If you appreciate lush and green surroundings you have come to the right place in an exclusive Kailua neighborhood.
Kapakai Cottage is a newly renovated modern ...
Registration Number: TA-181-128-3968-01
2 bedroom
1 bathroom
Sleeps 4
Hi> 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-02
4 bedroom
3 bathroom
Sleeps 8
HI> Kailua
Welcome to Lanikai Hale ~ Two Hawaiian style cottages on a large fenced-in property in the heart of Lanikai, just 2 miles from Kailua Town on the Windward side of Oahu.
There is plenty of parking and sleeping space for a group of 10 to 12 ...
Registration Number: TA-107-927-8080-01, GE-10
5 bedroom
3 bathroom
Sleeps 12
HI> Kailua