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, 20
"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
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
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.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. Recently remodeled kitchen and bath(s) finish off this stunning home. Whether you prefer lounging in the sun ... 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
Due to local laws we have a rental contract of 30 days. Registration Number: TA-143-873-0240-01, 430750280000, GE-143-873-0240-01
3 bedroom
2 bathroom
Sleeps 6
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
2 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
