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 Garden Estates%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Kihei Garden Estates #H-203 is a 2 bedroom, 2 bath unit on the second floor in a great location near the pool. It has a fully-equipped kitchen, fans throughout, 2 flat-screen TVs, a DVD player, telephone service, and free WIFI. There is a ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Unit #E-202 is a 2B/2B Loft-Style unit that is fully renovated. The unit is cooled with split system AC in the living room and loft bedroom; there are ceiling fans throughout. Enjoy the new 50" TV, BluRay Player, or Complimentary WiFi. Kihei ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Kihei Garden Estates is a lovely complex right across the street from Waiohuli Beach, which you can see from your private lanai. Maui’s famous sunsets are just steps away from your door. Please note: This unit is on the second floor with no ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Kihei Garden Estates #E-102 is a 1 bedroom, 1 bath ground floor unit with a queen bed, fully-equipped kitchen, flat screen TV, telephone with long distance calling, and free WIFI. Room unit a/c throughout as well as ceiling fans. For outdoor ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Kihei Garden Estates #E-204 is a two bedroom one bath unit with a fully equipped kitchen, Cable TV, and high speed internet service. The phone is also equipped with free long distance calling to the U.S. & Canada. Air conditioning room unit in ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
This Maui condo is located in the heart of Kihei Hawaii called Kihei Garden Estates, located within 300 feet of the ocean and 20 minutes from the Kahului Airport. Modern amenities, the condo is perfect for a family of 4 adults and 2 children ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Indulge in the ultimate Maui experience at our charming one-bedroom, one-bath ground-floor condominium in Kihei, Hawaii. Just steps away from the beach, Kihei Garden Estates G-102 offers a serene oasis amidst breathtaking ocean views and ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Discover beachside bliss in Kihei! this enchanting home captivates with its blend of cozy ambiance and unbeatable location. Steps away, discover a plethora of restaurants, shopping, dining, and coffee spots, all within walking distance. This ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
