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 and round(price)>0 and bedrooms>=0 AND (`uniturl` like '%acb38a63-c0e4-4f58-bdea-9eba2183d2e5%' ) AND sleeps>=1 order by rating desc LIMIT 0, 15
Haena Modern Beachfront, Hale Elua (Two) is a newly renovated modern beach home with luxurious furnishings. Hale Elua offers 3 bedrooms, 2.5 bathrooms, a fully equipped gourmet kitchen, a formal dining room table, casual outdoor dining on the lanai, air conditioning, and high-speed internet. There are two homes on the property that are available to rent individually and experience a beautiful private escape for your oceanside adventure or you may want to rent them both for a family reunion, small business conference, or group of friends.
General Amenities Include:
Air Conditioning in all bedrooms
Ocean views from living room, kitchen and master bedroom
Beachfront - located on a stunningly beautiful tropical beach with swimming and snorkeling
Spacious raised lanai (covered porch) with full ocean views and BBQ
Lush landscaping, walking paths and garden reading area
Beach chairs, mats, children's beach toys
Outdoor shower with hot & cold water for rinsing after the ocean
Bed, Bath & Kitchen Amenities Include:
Private master suite with ocean views and double vanity
One king bed, two queens, all with high count cotton sheets
Quartz countertops throughout
Sub-Zero refrigerator/freezer, oven, convection microwave, dishwasher, toaster, and blender.
Haena Modern Beachfront Hale Elua is located on the lush north shore ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Hanalei
