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 = 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, 15
***COVID 19 Restrictions: The State of Hawaii has updated their Safe Travels program. Please refer to https://hawaiicovid19.com/travel/faqs/ for the latest information about traveling to Hawaii and how to bypass the mandatory 10 day quarantine ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
HI> Waimea
Ola'a (meaning "lively" in Hawaiian) House is a 1,700 square foot home in the heart of Volcano Village featuring vintage Hawaiian furnishings and original art. The house is owned and has been thoughtfully updated by the co-founder of Kilauea ...
- 2 bedroom
- 1 bathroom
- Sleeps 5
HI> Volcano
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
Deluxe Panoramic Mountain View Condo - 37th Floor, Free parking & Wifi
Enjoy paradise with an incredible mountain view Waikiki Banyan condo suite only one block from world famous Waikiki Beach! Condo suite is located on the 37th floor and has ...
Registration Number: TA-024-432-8448-01
- 1 bedroom
- 1 bathroom
- Sleeps 6
HI> Honolulu
1.17 acres planted with palms and fruits trees, free parking free wifi.
guests can walk around house. Barbecue place next to ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
HI> Keaau
Commitment to clean - this home is professionally cleaned and disinfected between guests and there is a 24 hour minimum buffer between guests.
Escape to paradise in this oceanfront studio condo. Fall asleep to the sound of lapping waves or watch...
- 1 bedroom
- 1 bathroom
- Sleeps 3
HI> Princeville
State of Hawaii Transient Accommodations Tax License #TA-071-739-0848-01. County of Maui Short-Term Rental License #Hotel Zoned Resort. Tax Map Key #210080910071
Castaway Cove C201 is located on Maui island, ranked #1 Best Luxury Destination in...
- 3 bedroom
- 4 bathroom
- Sleeps 6
HI> Wailea
Beautifully renovated Waikiki studio in a fantastic location at the secure Kuhio Village building. Located right on Kuhio Ave., our guests enjoy easy access to the upscale Dukes Lane, newly built International Market Place, Kalakaua Ave and ...
Registration Number: TA-022-954-5984-01
- 0 bedroom
- 1 bathroom
- Sleeps 2
HI> Honolulu
Updated in 2014, this deluxe one-bedroom one-bathroom suite with Pacific Ocean views has modern features that beach-goers will love! This bight and airy suite has a beachy décor, modern island furnishings, and Hawaiian-style artwork. You’ll ...
Registration Number: GE-066-371-9936-01, TA-06
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> HONOLULU
This Oceanfront Condo has an amazing view. It has a gorgeous Great Room and a beautiful fully equipped kitchen. With the roar of the ocean just a few yards away, you will not want to leave the Lanai after relaxing on the Tropitone furniture.
...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kailua-Kona
Walk out of your condo and onto Maui's longest beach - direct access to serene Sugar Beach. No stairs or elevator to deal with.
Small, boutique atmosphere with RARE truly beachfront pool, deck and lawn.
If you are looking for the peace of ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kihei
Kahana Sunset Resort offers guests the ideal location to enjoy Maui. This beachfront resort is the only resort situated on the secluded, sandy beach of Keoni Nu'i Bay with pristine views of the beautiful blue Pacific. This direct oceanfront ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
HI> Lahaina
State of Hawaii Transient Accommodations Tax License #TA-094-662-2464-01. County of Maui Short-Term Rental License #Hotel Zoned Resort. Tax Map Key #210080910063
Royal Ilima A201 is located on Maui island, ranked #1 Best Luxury Destination in ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
HI> Wailea
Kahana Sunset Resort offers guests the ideal location to enjoy Maui. This beachfront resort is the only resort situated on the secluded, sandy beach of Keoni Nu'i Bay with magnificent views of Lanai and Molokai. This direct oceanfront two ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Lahaina
The LoveRentals portfolio is comprised of curated, professionally managed vacation rental homes around the world. We only select professional property managers that meet our high standards. Book with peace of mind knowing that you will have the ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Waikoloa
