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, 30
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. Kailua is a small upscale ...
Registration Number: TA-191-191-0400-01, GE-191-191-0400-01, 430130460000
4 bedroom
5 bathroom
Sleeps 8
Hawaii> Kailua
Calendar may not reflect accurate availability. Please contact owner with custom requests. Enjoy this lovely and elegant cottage, one short block from world famous Lanikai beach! This beautiful cottage boasts stunning acacia hardwood ...
Registration 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
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
This casual Kailua Beach house will transport and inspire you with its panoramic ocean views and is available to be rented for periods of 30 days or more at any one time. The 3 main bedrooms, living room and dining spaces offer incredible sites...
Registration Number: GE-023-497-1136-03, TA-023-497-1136-03
4 bedroom
3 bathroom
Sleeps 8
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
This beautiful four bedroom (two luxury master suites, one guest suite and guest bedroom, all air conditioned), four bath Hawaiian style home is just a short walk to pristine Kailua Beach. This spacious, estate quality home offers serene ...
Registration Number: TA-065-681-2032-01
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Kailua
Turn key ready for a relaxing stay. With on site parking, a common use gated entrance leads you down a lava rock pathway to a private gated courtyard that contains a BBQ, water feature, patio table & chairs and lighted umbrella to enjoy ...
Registration Number: 143024003000, GE-111-409-1520-01, TA-111-409-1520-01
1 bedroom
1 bathroom
Sleeps 2
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. It boasts 3 bedrooms en suite that are generous in size and more than ample in ...
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! 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...
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. The cottage is a 600 square foot 1 ...
Registration Number: 143072028000, GE-058-690-1504-01, TA-058-690-1504-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua
Our beautiful home has been featured in Hawaii Home Magazine and has received a best in class award from the Hawaii Builders Association. The home is within a 2 minute walk to Kailua Beach Park, the Kalapawai Market and Buzz's Steak ...
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
5 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
With front row seats to the ocean and the famous Mokulua Islands of Kailua, Lanikai Oceanside offers the best seats in the house. Newly renovated, this tropical chic and modern oceanfront beach home is open and spacious, yet warm and intimate. ...
Registration Number: GE-141-510-3488-01, 430030630000, TA-141-510-3488-01
5 bedroom
4 bathroom
Sleeps 10
Hawaii> Kailua
2 Night Minimum Stay 10 night Maximum Stay 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. ...
Registration Number: GE-168-049-0496-01, 430180480000, TA-168-049-0496-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua
Kaimalino Kai is a two story, 3 bedroom, 4 full bathroom oceanfront gem perched elegantly against the backdrop of Kailua Bay. Hear the sound of the waves crashing against the rocky shoreline while magical ocean views complement the elegant ...
Registration Number: 440390160000, GE-097-705-3696-01, TA-097-705-3696-01
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Kailua
Hale Kea is a newly renovated Hawaiian-style home designed for casual living and family entertainment; just steps to beautiful Kailua Beach. There are two master bedrooms with in suite bathrooms and a guest bedroom with a double bed. All...
Registration Number: 430100170000, TA-065-681-2032-01, GE-065-681-2032-01
3 bedroom
3 bathroom
Sleeps 5
Hawaii> Kailua
The upstairs and downstairs beach front studios..My beach front studios for 2 guests are located right on beautiful sandy Kailua Beach. they are quiet with private entrances and private bathrooms, modern, spacious and tastefully furnished. The ...
Registration Number: 430150470000, GE-027-534-9504-02, TA-027-534-9504-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua
Tropical Paradise with your own yard... Over 800 square feet of living. Our newly renovated cottage is the perfect getaway located in the heart of Kailua. Light-filled and breezy this is the perfect space for one or a couple, just ...
Registration Number: TA-133-517-5168-01, GE-133-517-5168-01
0 bedroom
1 bathroom
Sleeps 3
Hawaii> Kailua
Paradise is now more affordable than ever. E komo mai to Hale Makai, our piece of island paradise in Kailua. Beachside and just three houses from Kailua Beach - you are going to love it here. Lying alongside a fabulous three-mile ...
Registration Number: GE-203-074-9696-01, TA-203-074-9696-01
3 bedroom
2 bathroom
Sleeps 6
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-01 Beautiful ocean front home at the beginning of Oahu's famous Lanikai Beach!. 2 bedroom, 1 bathroom main house and 2 bedroom, 2 bathroom guest cottage. Featuring breathtaking views, a brand new courtyard pool with ...
Registration 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. This garden oasis (we've planted over 3,000 trees and plants) is in the most awesome location - situated right at the ...
Registration Number: GE-077-835-2640-01, TA-077-835-2640-01, 430110790000
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua
This charming and cozy two bedroom Kailua cottage is a perfect retreat for the budget minded traveler desiring a beach side getaway. Beautifully remodeled with attention to every detail this home is just steps from Kailua Beach Park and a short ...
Registration Number: 430090160001, GE-119-980-8512-01, TA-119-980-8512-01
2 bedroom
2 bathroom
Sleeps 4
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
VIEWS!! Absolutely beautiful right on Kailua beach with pool and spa . Six bedrooms of beach-front paradise in Kailua. Swimming pool, hot tub, game and TV room make this property an ideal vacation recreation ...
Registration Number: GE-095-587-3792-01, 430830080000, TA-095-587-3792-01
5 bedroom
5 bathroom
Sleeps 12
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