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 '%special%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Make your trip to Maui more memorable with a stay at our 5 star condo at Luana Kai. Luana Kai is a beautiful resort with immaculate grounds in a park like setting, ocean front in sunny Kihei with no busy roads to cross to get to the ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Kauai Haven is nestled within a classic Hawaiian style backdrop, resting on the edge of a bluff overlooking the spectacular Pacific Ocean. An award-winning designer, who is part of the Coastal Living Network of designers, has designed this ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Ask about 10 day specials/ 2 person stays and 8th night free if you book a 7th night stay!!!!
https://www.youtube.com/watch?v=VigSB3M4z3w
The above is the link to a video of my house.
Makai Hideaway is quiet and peaceful. Unwind and ...
- 3 bedroom
- 3 bathroom
- Sleeps 10
Hawaii> Captain Cook
Maui Permit #BBPH 2009/0002 , Hawaii Permit #SUP2 2002/0005
Hawaii Tax I.D. TA035-442-8928-01 TMK (2)2-7-035-010-0000
Maui's Ultimate Private, Romantic Cottage! ...a unique architectural Polynesian Pole home on Magical Maui. ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Haiku-Pauwela
Located in central Kihei, nestled on the northern end of beautiful Halama Street. Family-owned and operated legal short-term rental. This ocean front, 2 BR 1 BA unit, has a full kitchen, bathroom and a sofa sleeper pullout in the living room. ...
- 2 bedroom
- 1 bathroom
- Sleeps 5
Hawaii> Kihei
Located in central Kihei, nestled in on the northern end of beautiful Halama Street. Family-owned and operated legal short-term rental. This ocean front, 1 bedroom 1 bath unit, has a full kitchen and bathroom. Cozy, quiet, great for couples and ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Kihei
OCEAN VIEW
BOOK WITH US TODAY!
BEST REMODEL in MAUI VISTA
Completely gutted with NEW EVERYTHING!!!
Tigerwood Hardwood Floors, Italian Tile, Travertine, Fresh Paint, Ceiling Fans, All New Furniture, New Mattress...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Come enjoy the beauty of Hawaii in our perfectly located condo at Waikoloa Beach Villas. Just 20 minutes north of the Kona airport you will find yourself in paradise!
Our property is perfect for 2 couples or a family of 3. Decorated to ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Waikoloa Village
Come enjoy our spacious 1 bedroom, 2 bathroom condo in one of the most beautiful spots in all of Maui. Located directly on the 4th fairway of the Kapalua Bay Course can be both entertaining and comical as you watch the golfers pass by. A ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Lahaina
Your dream vacation awaits you on the Island of Maui in this beautifully upgraded condominium located across the street from Kamaole II Beach Park. Surrounded by lush tropical landscape, Maui Banyan is just steps away from restaurants, shops, ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Top to bottom fully renovated 2 bdr 2 bath 1244 sq ft condominium in Maui Kamaole. Largest floor plan in the complex.
Enjoy ocean views and the quiet location of our unit located in the D Cluster of Maui Kamaole.
Tastefully ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Hale Halia is just steps from Poipu Beach park with unparalleled oceanfront views and is a great family vacation destination. This lovely two-story floor plan is a design that takes full advantage of the magnificent Poipu coastline and sunset ...
- 4 bedroom
- 3 bathroom
- Sleeps 10
Hawaii> Koloa
Welcome to the Waipouli Beach Resort and Spa! The premier resort on Kauai!
Waipouli Beach Resort and Spa is located beach front on the Coconut Coast between the historic towns of Kapaa and Wailua. Located convenient to both Princeville...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kapaa
***The pool has just been renovated and is gorgeous beginning November 1st 2024***JUST RENOVATED DESIGNED BY A NYC ARTIST. THIS BEAUTIFUL CONDOMINIUM IS BORDERING KIHEI AND WAILEA IN SOUTH MAUI, HAWAII. IT IS LOCATED AT THE GORGEOUS MAUI HILL ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
THE LIVE ALOHA SUITE is a tastefully designed condo by Stannard Architects, from international design firm, Olson Sundberg. It is a reflection of the tropics, well appointed, fresh, clean and lovingly cared for by the owners. This gem has a full...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
$350/NT+TAX SPECIAL PLUS $100 LAVA LAVA BEACH CLUB CARDS FOR A LIMITED TIME!
UNBELIEVABLE SPACIOUS TOWNHOME ILLUMINATED WITH A WALL OF NATURAL LIGHT!
Kulalani 904 is one of the newest homes developed within the resort. At a ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Kamuela
Anchor Cove D2 is a single story property perched at the beachfront on the beautifully landscaped grounds of Hale Pau Hana Resort. Originally the resort restaurant, the building was reconstructed as a side by side duplex. Its prime location, ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
SEE OUR MARCH 25% SPECIAL
This is the perfect SUITE located in the east of the property for an outdoor lover who wants to feel the connection with nature and enjoy of an amazing...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Thank you for looking at our home for your vacation. Though we are truly saddened by the devastating fire in Lahaina, we are continuing our support for many as they recover from this deadly incident. As part of our help in this recovery process,...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kapalua, Maui
CHECK OUR MARCH 25% SPECIALS
These Suites are in a beautiful setting. You will hear the waves crashing. See the turtles in front of your vacation Suite. Enjoy the wonderful warm Pacific Ocean, the waves crash in...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Relax in the beautiful three-bedroom seaside villa in Kailua, where soothing sea waves and soft breezes carry you off. Indulge in the outdoor pool, where you can soak up the sun and relax and end a day of hiking or golf with a massage in the ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
Hawaii> Kailua
You are sure to enjoy a memorable vacation in this fully furnished, spacious retreat in an ocean view studio premium villa at The Westin Ka'anapali Resort Villas. Maui's boundless beauty and activity begin and end here!
The Studio Premium...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
OFFERING DISCOUNTS FOR MAY 28 TO JUNE 2!
This large 1400 square-foot, two-bedroom, single level, beautifully furnished condo has spectacular ocean views from the front lanai, living room, master bedroom, and kitchen. This third-floor ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
You will love this luxurious, air conditioned, two bedroom, two bath, 1300 square foot Villa! Fully furnished, with its own spacious, private dining lanai, this unit was completely remodeled in 2020. It is immaculate, impeccably maintained and ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailea
Unit B1 is Ocean Front at Sunset Hale. Newly updated 2-bedroom 2.5-bath ground floor oceanfront unit.
The Kitchen, living room and dining area are all facing out to beautiful ocean and sunset views! Enjoy these views from inside or ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Haleiwa
MARCH MADNESS 🌅
SPRING BREAK ESCAPE
CHECKOUT LAST MINUTE SPECIAL RATES
Escape to Hawaii for a great vacation !!
Great condo , Great Location, Great Rates
Book your stay today for your next getaway!!
(SLEEPS 2 ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
FOR A WOW EXPERIENCE! Less than an hours drive from Honolulu (yet only 10 minutes from local shops and restaurants) but a world away from big city life. If you want to relax and enjoy the ocean, beach, mountains and get a chance to see the real...
- 3 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Waianae
Our beautifully appointed condo is perfect for a family (6 maximum), a couple or two couples (as there are 2 primary bedroom suites, each with a king size bed, private bath and flatscreen ). The condo features a spacious living and dining area ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Aloha future guests! All properties in our ohana or family have Hawaiian names. We call this Kapalua Golf Villa, Hale O Alihilani', which means 'home where the ocean meets the sky'.
JUST LIKE IN THE PICTURES, QUALITY RENOVATION, FABULOUS ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Lahaina
SPECIAL RATES for Summer/Fall 2025!
Relax in what feels like your open-air home in paradise!
Stunning views. Savor the outside air and breezes on your lanai. Superbly maintained. Consistent rave reviews (for years as we are ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Ko Olina
Prices start at $150,000/month and will not be pro-rated based on length of stay. Please inquire to get an accurate quote.
Banyan House is Oahu’s most luxurious vacation rental near the foot of Diamond Head. The exquisite home features...
- 8 bedroom
- 10 bathroom
- Sleeps 16
Hawaii> Honolulu
Discover the delightful onebedroom getaway nestled right within Haleiwa. Nestled just a 22-minute drive from the renowned Banzai Pipeline and 24 minutes from the stunning Turtle Bay Beach, this cozy retreat offers the perfect base for your ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> haleiwa
RAINBOWS & TROPICAL BREEZES
Aloha! Welcome to our condo at the Plantation in Princeville, on the beautiful Garden Island of Kauai. Come and enjoy Rainbows, Sunsets and Tropical Breezes. Our Hale faces East which allows for the ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Princeville
Yes you can afford this great vacation in our wonderful condo, located near Wailea . We are in the Hale Kamaole resort and are on the ground floor.
Features include:
1. Walking distance to Kamaole Beach 3 (it's right across the street :) ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Welcome to the newly renovated, top floor ''Turquoise Cove'' Villa - Makena Surf F301. This amazing property has unsurpassed ocean views, with a unique, double wrap-around lanai, perfect for enjoying tropical sunsets or catching the occasional ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Wailea
First, to see pictures of the rooms, please go to the vistana website. Google THE WESTIN KĀ‘ANAPALI OCEAN RESORT VILLAS NORTH. Then look up TWO-BEDROOM LOCKOUT VILLA. They have tons of photos, better than we can take.
This area is ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Lahaina
This beautiful 1850 square foot 3 bedroom / 3 bath town home is a very private end unit which has a wall of windows that fill the living area with natural light. On the main floor, you will find a gourmet kitchen with granite counter tops and ...
- 3 bedroom
- 3 bathroom
- Sleeps 7
Hawaii> Kamuela
Our beautiful condo is located on the world famous Kaanapali Beach and shores. You can't get any closer to the ocean then the MAUI KAI Resort, the Bldg. Is just feet away with the best snorkeling right outside the front door. As you relax on ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina, Maui
Welcome to West Maui! You’ll love this spotless tropical Maui Eldorado condo in heart of the beautiful, world famous Kaanapali Beach. This updated unit has everything you’ll need for a wonderful vacation on Maui! Maui Eldorado is ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kaanapali
WELCOME TO OUR HAWAIIAN RETREAT in Wailea!
We purchased our second property in Wailea - November 2019. Enjoy your getaway at our new home-away from home! We spent the 2019 holiday season buying new furniture for EVERY room, decorating, ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kihei
Welcome to @Maui Makai Condo!
This lovely 1 bedroom, 1 bathroom condo is situated in the hotel zone and just a stone's throw away from Kahana Beach in West Maui (conveniently nestled between Ka'anapali and Kapalua).
Following ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Napili-Honokowai
Magnificent large 7th floor end unit condo, considered the corner suite, is fully equipped with new furnishings, including barn doors, new ceilings, new bathrooms, new kitchen, new flooring and ultra modern features throughout. This condo has ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Luxury Ocean View Condo Hainoa Villa 2905B at Four Seasons Resort Hualalai Make your dream vacation in Hawaii come true! This gorgeous upper level two bedroom/two bath villa is tastefully designed and appointed. The pristine condition reflects ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Holualoa
The much coveted ground floor, one level, 2 bedroom - 2 bathroom floor plan at Papakea. No steps! Also, brand new ductless air conditioning system in both bedrooms and living room / kitchen area. An amenity not all of the condos have! Enjoy ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Welcome to Wailea Elua #1002! This recently renovated luxury beachfront 2 bedroom/2 bath residence is located in the heart of Wailea on Maui Sunny South Shore. Within steps of the unit you are on one of Maui’s best beaches. Ulua Beach is ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Located in central Kihei, nestled on the northern end of beautiful Halama Street. Family-owned and operated legal short-term rental. A cozy 2 bedroom, 1 bath cottage with a large deck for sunbathing or BBQS. Less than a three-minute walk on the ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Stay longer, enjoy more! Book 10+ nights and receive a free mid-stay clean. Book 14+ nights and get one night free!
This property is hotel zoned, and not being affected by the recent proposed legislation.
Welcome to this luxurious ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
Hawaii> Kihei
Relax in paradise! Top floor condo with a one level floor plan. All new furnishings, carpet and paint. Lovely lanai to watch the golfers go by and enjoy the views and smells of the island of Maui. There are 4 pools in the Kapalua Golf ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Lahaina
Kolea 3B Special Rate $550/nt+tax Plus $100 Lava Lava Beach Club for a limited time!
3 bedroom, 3.5 bath
2nd floor level with semi private elevator
Partial ocean and mountain views
Master suite king bed with private lanai, ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
Hawaii> Waikoloa
Aloha and welcome to paradise! Watch the whales, turtles and schools of dolphins from our beautiful ocean front condo!!!
Located on the second floor, this unit feel like you are flowing into the ocean from the living room and master ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
