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 '%Lahaina Shores%') AND sleeps>=1 order by rating desc LIMIT 0, 20
This beautiful studio is on the 4th floor at the ocean-front Lahaina Shores Resort condo. It locates on Lahaina's Famous Front St. with a short walk to shops, restaurants, wharf and Lahaina Town. The Mountain Views from the condo are ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
My 6th Floor Oceanfront Condo at Lahaina Shores has drop dead panoramic views of Lanai. Watch the whales breeching from the balcony, enjoy the warm tradewinds and walk to the historic whaling town of Lahaina which has renowned restaurants, ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
My 4th Floor Oceanfront Condo at Lahaina Shores has sweeping views of Lanai. Watch the whales breeching from the balcony, enjoy the warm tradewinds and walk to the historic whaling town of Lahaina which has renowned restaurants, nightlife, live...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
Sorry, Due to the Maui wildfires, at the present time we are no long taking any reservations until the extent of damages has been assessed and when we will be able to get back online!
E komo mai! Welcome to your vacation in paradise! ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
Please note we accept only VISA and MasterCard.
At Lahaina Shores Beach Resort you feel miles away from the crowds, yet you can walk into the historic town of Lahaina in just five minutes. Lahaina Shores is a great place to stay if you ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
GREAT LAST-MINUTE BOOKING SPECIALS - CHECK OUR CALENDAR FOR YOUR PREFERRED DATES!
This is the 6th floor condo that satisfies all your desires for your Maui Dream vacation!!
This beautifully redecorated studio is a perfect balance...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
Note: The pool and spa facilities at the property will be temporarily closed for use from 24th April to 2nd June 2023. This is due to the scheduled Homeowners Association's maintenance work. Furthermore, please note that this condo's lanai will ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
Our lovely Maui condo has beautiful countertops and paint, new flooring, Cal-King bed, twin sofa sleeper, shower/tub, free high speed internet, DVD player, fully equipped kitchen including refrigerator, electric range, coffee maker, Keurig, ...
- 0 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Lahaina
Welcome to Lahaina Shores 218 right in the middle of downtown Lahaina. Best location, beachfront resort on Front Street! This cozy studio offers views of the West Maui mountains from your private lanai. Complete with all you need for the perfect...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
As an Xplorie activity partner you enjoy FREE activities every day of your booking. Inquire for more details!
Lahaina is the Royal Playground of the Hawaiian Nation. Lahaina Shores Beach Resort will put you as close to the beauty of the ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Enjoy all the amenities of this first-class resort beachfront hotel, Lahaina’s warm sunshine, and a beautiful white sand beach. You'll be staying in a Mountain View Studio condo with full kitchen & central AC at Lahaina Shores. The Lanai ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
*As a result of the Maui fires in August, operations at Lahaina Shores Beach Resort are suspended until further notice. Please check back with us in the future for your vacation stay...
A Premier Studio Mountain View on the 5th floor ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
*As a result of the Maui fires in August, operations at Lahaina Shores Beach Resort are suspended until further notice. Please check back with us in the future for your vacation stay...
Elevated views of the West Maui Mountains and ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
*As a result of the Maui fires in August, operations at Lahaina Shores Beach Resort are suspended until further notice. Please check back with us in the future for your vacation stay...
This is Lahaina Shores at its best with stunning ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
