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 and b.blocked=0 ) as vw
WHERE 1 = CASE
WHEN postalCode IN (96701,96706,96707,96712,6717,96730,96731,96734,96744,96782,96786,96789,96792,96826,96795,96797,96813,96814,96815,96816,96817,96818,96819,96821,96822,96825)
THEN
CASE
WHEN (registration_number like '%TA-%' or registration_number like '%TA %') THEN 1
ELSE 0
END
ELSE 1
END
and postalCode!=''and round(price)>0 and bedrooms>=0 AND sleeps>=1 order by rating desc LIMIT 0, 20
Punahele Penthouse Condo will fast become your favorite place to stay in Kailua-Kona! Punahele Penthouse is on the 7th (top) floor of the Kona Ali'i Condo Complex which is located in the heart of Kailua-Kona. You're within walking distance to ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Paradise awaits you in this beautiful ground floor, one-bedroom, two-full-bath Lae Nani Condo, Unit #317 on the beautiful Garden Isle of Kauai!
Located on Kauai’s beautiful Coconut Coast (East side), this Condo has all the amenities you...
- 1 bedroom
- 4 bathroom
- Sleeps 4
Hawaii> Kapaa
Hale Nalu is our beautiful luxury Beach Villa in Ko Olina on the island of Oahu! Our fully equipped central AC 4th floor villa located in the Beach Tower at the gorgeous Beach Villas, unit #406. It’s situated on Lagoon 2. We offer LOCATION ...
Registration Number: GE-004-134-3488-01, TA-004-134-3488-01, 910570090195
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kapolei
Guests walk in and we see wow on their faces...gourmet kitchen, ocean views, huge bedrooms.
"perfect for a family reunion"
"cool at night was just right"
"close to the best beach in Hawaii"
"your own private estate"
"amazing...
- 5 bedroom
- 6 bathroom
- Sleeps 18
Hawaii> Kailua-Kona
We invite you to stay in our peaceful home on the North Shore of Maui, within walking distance to a local coffee shop, small grocery and near the Road to Hana and Haleakala. Only available to book with the owner Amy - Beware of scammers ...
- 2 bedroom
- 1 bathroom
- Sleeps 5
Hawaii> Haiku
Dream your most desired vacation. Then come to magical Maui, one of the world’s most elite vacation destinations. How better to experience our island than by renting from an owner who has combined her own personal getaway with the conveniences...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Maalaea
This most sought after location in the complex is a ground floor corner unit offering privacy and just 10 paces away from one of the two almost private beaches, where you may find yourself alone.(Be aware that the beaches are subject to ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
Discover the ultimate Kauai getaway at Ka Pua Hale, our vacation rental also known as "Blossom House." Nestled strategically in Princeville, Kauai, our rental provides unmatched convenience:
- Close proximity to North Shore beaches, golf ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Princeville
Gorgeous, completely upgraded oceanfront/oceanview 2 bedroom, 2 bath. This condo is exquisite and has been meticulously renovated for the discriminating traveler. Huge, unobstructed ocean and island views from the living room, lanai and master ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Love to snorkel and want to see over 100 species of tropical fish & turtles? Then why not stay right across the street from Hawaii's best snorkeling beach?! Our modern beachfront unit offers gorgeous ocean/sunset views and the easiest access ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
SPECTACULAR VIEWS await you at Kahoa House for your Hawaiian holiday. Early-birds and mainlanders adjusting to the time difference can enjoy a cup of Ka’u coffee in the comfortable living room, and watch the sun rise out of the Pacific ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Hilo
Kauhale Makai (Village by the Sea) is the epitome of paradise. This newly remodeled 2-bedroom, 2-bathroom condo has been completely transformed, featuring a brand-new kitchen with granite countertops and top-of-the-line appliances.
The ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
ALOHA. Come vacation in our beautiful, newly remodeled 2BR/2BA beachfront home. Enjoy the breathtaking views of Kaho’olawe Island, Molokini Crater, the West Maui Mountains and of course the pristine Pacific Ocean and its shoreline. Sugar ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
"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."
Come enjoy island life while staying in our newly built, 600 sq. ft. upstairs suite. Beautifully ...
Registration Number: 420200410000, TA-071-652-3520-01, GE-071-652-3520-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua
Make your trip to Maui more memorable with a stay at our 5 star condo at Luana Kai. Luana Kai is a beautiful resort with immaculate grounds in a park like setting, ocean front in sunny Kihei with no busy roads to cross to get to the ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
This is an end unit on the first floor at the back of the resort complex. You can enjoy the lush gardens and tropical breezes from the spacious lanai. The newly renovated condo has a master bedroom that includes a king size bed. The main living ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
The Artists House, Permit # STLA 2017/0002 is a home away from home with the comfortable furnishings and great amenities. The fabulous Lana'i sunsets can be enjoyed outside on the deck where there is glass dining room table with 6 chairs and bbq...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Lanai
Villa 3 at Villas By The Cove is a stand-alone, single story 1 bedroom Villa Suite with ocean views. Its prime location is just steps from two of South Maui's most popular white sand beaches. Newly remodeled, and conveniently located, this 600 ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
This unit is B402 in the Beach Tower, closest to the beach.
Promo: Book 7 Nights and you will receive the 8th Night Complimentary (dates may vary based on the season).
We have a dedicated housekeeping team which details the condo before ...
Registration Number: TA-134-204-4160-01, GE-134-204-4160-01
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kapolei
Emmalani court is a small, quiet complex in Princeville, near the Princeville Resort. After visiting the 4 main islands in Hawaii, we decided on the North shore for it's lush setting and laid back vibe. There is truly no other place like it in ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
