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 (`headline` like '%Kihei Sands%') AND sleeps>=1 order by rating desc LIMIT 0, 15
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
OCEANFRONT GORGEOUS Condo A1, Kihei Sands, 1 Bedroom, 1 Bath, sleeps 2, “KAHAKAI” (BEACH) Condo - A1!
Aloha & E Komo Mai to our beautiful Kahakai Condo, Unit A1~
We recently purchased and completely renovated A1, we love spending time here...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kihei
Aloha and Welcome to Kihei Sands Beachfront Condominiums Condo B9 in sunny South Maui! Condo B9 boasts an unobstructed 180 degree up-close-and-personal front row view of the beach, the Pacific Ocean, and Maui's spectacular nightly sunsets.
...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kihei
Beautifully Renovated to the studs BEACHFRONT ground floor 1 Bedroom/1 Bath Condo at Kihei Sands (Sleeps 2 Max).
There’s something about the salty air and lull of the tide that encompasses the charm of Coastal Beach living. The condo is a ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kihei
Aloha and Welcome to Kihei Sands Beachfront Condominiums Condo A03 in sunny South Maui! Condo A3 is a one bedroom, ground floor condo unit with a peek-a-boo ocean view, that sleeps 2. Ceiling fans in every room, including bath and kitchen. ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kihei
Aloha and welcome to Kihei Sands, located in North Kihei just steps away from Sugar Beach with views in either direction!
B3 has been completely renovated, using only the highest quality furnishings, appliances, linens, furniture and details, ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kihei
Aloha and Welcome to Kihei Sands Beachfront Condominiums Condo A9 in sunny South Maui! Condo A9 has beautiful new wood look tile, new living room furniture, remodeled bathroom and new kitchen appliances. We also have new Mitsubishi A/Cs to keep...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kihei
Aloha and Welcome to Kihei Sands Beachfront Condominiums Condo B11 in sunny South Maui! Condo B11 is one of the premier 2-bedroom units in the complex. The only true 2-bedroom where the master is not a loft, as the upper floor is completely ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kihei
Aloha and Welcome to Kihei Sands Beachfront Condominiums Condo A10 in sunny South Maui! Condo A10 has new Mitsubishi A/Cs, a beautiful new kitchen, and an incredible built in living room TV system.
Kihei Sands Condominium sits directly on one ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kihei
Aloha and Welcome to Kihei Sands Beachfront Condominiums Condo A13 in sunny South Maui! Condo A13 was completely remodeled in June 2015. Enjoy new Mitsubishi A/C and free WiFi in this beautiful and comfortable vacation home.
Kihei Sands ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kihei
Aloha and Welcome to Kihei Sands Beachfront Condominiums Condo B5 in sunny South Maui! Come and enjoy the stunning ocean/beach view from your living space and lanai, as well as Mitsubishi air conditioning in all living areas, free WiFi, modern ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kihei
Aloha and Welcome to Kihei Sands Beachfront Condominiums Condo B14 in sunny South Maui! The interior of this condo is ALL NEW due to a recent complete remodel, including new bathrooms, new cabinets and appliances in a fully equipped kitchen, ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Kihei
Aloha and Welcome to Kihei Sands Beachfront Condominiums Condo A7 in sunny South Maui! Newly remodeled Condo A7 has Travertine floors throughout and a wonderful tropical feel. You'll stay cool with Mitsubishi AC and the condo sits only one ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
HI> Kihei
Aloha and Welcome to Kihei Sands Beachfront Condominiums Condo B8 in sunny South Maui! Condo B8 has a newly remodeled kitchen, new living room furniture and new Mitsubishi A/Cs.
Kihei Sands Condominium sits directly on one of Maui’s best ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
HI> Kihei
Aloha and Welcome to Kihei Sands Beachfront Condominiums Condo A11 in sunny South Maui! Condo A11 has a remodeled kitchen with granite countertops, new cabinets, new living room furniture and new Mitsubishi A/C in the main living area.
Kihei ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kihei
