select * from (Select a.`postalCode`,a.`uniturl`, `region`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `source`, `property_type`, `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 and round(price)>0 and bedrooms>=0 AND (`headline` like '%Kihei Sands%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Are you craving PANORAMIC views from your condo & private balcony? Condo B9 boasts unobstructed 180° up-close-&-personal views of white sand beaches, Pacific Ocean, West Maui Mountains & North Kihei's resplendent sunrises AND ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> 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...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
We can't wait to welcome your party of 4 to Kihei Sands Beachfront Condominiums B8 in sunny South Maui! Condo B8 is a 2-bedroom, OCEANFRONT townhouse-style condo on the 2nd level (no elevator) with stunning views of Haleakala, West Maui ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Welcome to Kihei Sands Beachfront Condominiums Condo A11 in sunny South Maui!
Why not join us for some sunshine, sand & sea while prices are at their lowest in years? Book your vacation condo & get ready for an unforgettable trip. ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
This COMPLETELY remodeled & refurnished (April 2022) 1-bedroom, OCEANFRONT ground-floor condo has a king bed as well as a sleeper sofa in the living room - great for those who wish to sleep separately OR bring up to 2 extra persons. This condo ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Now's the time to book your holiday vacation condo & get ready for an unforgettable family trip together! This high-end condo was impeccably remodeled with your comfort in mind. Kihei Sands' only true 2-bedroom unit has a full upper floor - no ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Are you looking for a quick trip to Maui for a special occasion - or just need to get away? Leave the cold & gray skies behind & come get some sunshine on your bones this Spring or Summer. Prices have never been lower! If you're looking for an ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
ALOHA! Are you ready to spend some time barefoot at the beach? Let us welcome you to Condo A7! All you'll need is a carry-on full of swimsuits & the slippahs on your feet when you arrive at Kihei Sands Beachfront Condos in sunny South Maui. Our...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Kihei
Are you looking for a quick trip to Maui for a special occasion - or just need to get away? Our summer & fall prices are low-low-low! If you're looking for an unpopulated spot within a 5-10 mile drive of dining, shopping & nightlife, Kihei ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Are you ready for a beach vacation? Surprise your sweetheart with an off-season trip to the island of Maui and join us here at Kihei Sands! With many airfare deals going on right now, there's never been a better time to book a trip. Come join us...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> 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
Hawaii> Kihei
Aloha & welcome to Condo B6, a 2bd/2ba Superior Ocean View townhouse-style home on the 2nd level at Kihei Sands Condominiums!
This condo is priced for 4 persons - we'll add $42/pp/night for each additional guest over 4, up to 6 guests total....
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Condo B4 is a beautifully renovated 2B/2Ba unit with the highest quality furnishings, appliances, linens and furniture, all with your comfort & enjoyment in mind. Beach towels, beach chairs, cooler & umbrella provided for your use. This condo ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Welcome to Kihei Sands Beachfront Condominiums Condo B10 in sunny South Maui! This second-story (no elevator) condo sleeps 4 with a queen bed in the downstairs bedroom & a king bed in the upstairs loft bedroom with ensuite bathroom. Perfect for ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
ALOHA! Are you ready to get out of the heat & join us for sunshine & breezy days, cool air conditioning, warm waters & beach living? Now's the time to book your summer or fall vacation condo & get ready for an unforgettable family trip ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Condo A8 is a highly desirable 1-bedroom, (sleeps 2) ground floor OCEANFRONT CONDO at Kihei Sands Condominiums & has been COMPLETELY remodeled & refurnished. Our convenient & central location is less than 15 mins from OGG airport. We're the ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Are you ready to join us for sunny days, warm water & some Maui magic? Off-season is the perfect time to book your summer or fall vacation condo & have an unforgettable family trip together. Family reunion, birthday or anniversary on the ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Condo B5 is a 2B/2Ba (Sleeps 4) townhouse style condo on the 2nd level (NO ELEVATOR) at Kihei Sands Condominiums. Our beautiful condo was remodeled with your comfort in mind. Gorgeous Hawaiian art graces the walls.
Located on Maui's ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Welcome to Kihei Sands Beachfront Condo B12 in sunny South Maui! Off-season is the perfect time to book your summer or fall vacation condo & have an unforgettable family trip together. Family reunion, birthday or anniversary on the horizon? No ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
