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 IF (ISNULL(`bedrooms`), 1,0), `bedrooms` desc LIMIT 0, 15
Hawaii Magazine has rated Kailua Beach on Oahu as the best beach in the state of Hawaii, and you have an oceanfront estate with two houses that are available to rent in paradise. These two magnificent homes both sleep 12 people (24 total) is ...
Registration Number: GE-210-931-9168-01, TA-21
- 12 bedroom
- 12 bathroom
- Sleeps 24
HI> Kailua
Welcome to the Aloha Villas Resort! Located in South Maui on beachfront property, this Luxury Resort is a prime vacation spot. Featuring approximately 2,000 square feet (186 meters) of oceanfront living, with 4 luxury villas, each with 3 Bed/3 ...
- 12 bedroom
- 14 bathroom
- Sleeps 26
HI> Kihei
Kaanapali Luxury Vacation Rentals welcomes you to our beautiful Luana Garden Villas at the Honua Kai Resort.
This listing is for “four” separate neighboring villas in one building, which is perfect for a multi-family vacation. Luana Garden ...
- 12 bedroom
- 12 bathroom
- Sleeps 32
HI> Lahaina
-INTRODUCING DEEP CLEAN EVERY TIME:
At Maui Resort Rentals the health and safety of our guests and team members is of utmost importance to us. Our commitment to cleanliness is embodied in our nearby 8000 sq. ft. operations warehouse and our ...
- 12 bedroom
- 12 bathroom
- Sleeps 32
HI> Lahaina
Welcome to koolkondo where you will experience luxury vacation rentals at its finest. Our stylish boutique hotel style condos and homes have it all. You will absolutely enjoy your home away from home with VIP concierge style services in modern ...
- 11 bedroom
- 1 bathroom
- Sleeps 22
HI> Koloa
UPDATE: Guests coming after August 15th 2021 will enjoy our new constructed private pool with slide.
Welcome to Maui's most desirable vacation home! Your own private mansion on the water in Lahaina (this home is partial ocean front). This...
- 11 bedroom
- 11 bathroom
- Sleeps 32
HI> lahina
Hawaii Island Retreat's eco-boutique hotel and wellness spa is a place of intentional peacefulness, set among towering evergreens and ocean breezes along Hawaii Island's North Kohala coast. When you visit Hawaii Island Retreat you quickly ...
- 10 bedroom
- 10 bathroom
- Sleeps 35
HI> Kapaau
Indulge yourself in the ultimate island style luxury at the Big Island of Hawaii’s most exclusive retreat, Kauhale Pilialoha. Experience breathtaking sunsets, enjoy the natural beauty of Hawaii from the ultimate poolside lanai perched on the ...
- 10 bedroom
- 14 bathroom
- Sleeps 30
HI> Kamuela
Our overall Estate is 3 luxury oceanfront vacation rentals on the Kona Coast rented as 1 Estate/Home.
This package rents the 3 homes (as a package) as (1) home; shared common grounds on Estate.
Please see our other units, or if you need more ...
- 9 bedroom
- 7 bathroom
- Sleeps 22
HI> kona
Welcome to Haiku House Maui - a private and exclusive nine bedroom luxury plantation estate located on 20 acres of pristine Hawaiian land and overlooking the Pacific Ocean.
The paths around the property lead through a stunning arboretum, with ...
- 9 bedroom
- 11 bathroom
- Sleeps 20
HI> Haiku
Note:
1) Please always send messages to the host to confirm the schedule, how many guests, and other related questions before make your booking.
2) GREAT PRICE!
3) Be rented as monthly rental per local government’s requirement.
4) Per local...
Registration Number: 1410060400000, TA-111-933
- 9 bedroom
- 5 bathroom
- Sleeps 13
HI> Waimanalo
-INTRODUCING DEEP CLEAN EVERY TIME:
At Maui Resort Rentals the health and safety of our guests and team members is of utmost importance to us. Our commitment to cleanliness is embodied in our nearby 8000 sq. ft. operations warehouse and our ...
- 9 bedroom
- 9 bathroom
- Sleeps 24
HI> Lahaina
This luxury vacation rental sits on nearly 60,000 sq. ft. of prime Kailua Beach property with 225+ sq. ft. of linear beach frontage featuring soft sand, blue water and spectacular views of Kailua Bay. Two separate rental houses by award winning ...
Registration Number: TA-130-529-4848-01, GE-13
- 9 bedroom
- 8 bathroom
- Sleeps 14
HI> Kailua
-INTRODUCING DEEP CLEAN EVERY TIME:
At Maui Resort Rentals the health and safety of our guests and team members is of utmost importance to us. Our commitment to cleanliness is embodied in our nearby 8000 sq. ft. operations warehouse and our ...
- 9 bedroom
- 9 bathroom
- Sleeps 24
HI> Lahaina
“Notice: In compliance with the Revised Ordinances of the City and County of Honolulu, the Property will be made available for your use and occupancy for a period of thirty (30) days.”
$79.00 Admin Fee is for our Damage Protection Policy....
Registration Number: TA-063-319-9104-01, GE-06
- 9 bedroom
- 8 bathroom
- Sleeps 20
HI> Waimanalo
