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 (registration_number like '%TA-%' or registration_number like '%TA %') AND (city = 'haleiwa' OR postalCode = 'haleiwa' OR region like '%|haleiwa|%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Beautiful, fully renovated, 3/1 on very private acre above Ke Iki Beach. House is classic, old Hawaii style with sweeping ocean views. Short walk or bike ride to Pipeline, Waimea, Sunset. Easy access via bike path to miles of world class surf ... Registration Number: 590080020000, GE-018-615-0912-01, TA-018-615-0912-01
5 bedroom
2 bathroom
Sleeps 6
Hawaii> Haleiwa
Private, detached, gorgeous, newly built, fully equipped, one bedroom guest house ready and waiting to be booked by you! Superb location! Short walk to beautiful parks and beaches and minutes drive to historic Haleiwa Town.Registration Number: GE-103-420-9280-01, TA-103-420-9280-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Haleiwa
This upstairs, 3-bedroom 2-bath home, with its spacious open floor plan, oversized rooms, vaulted high ceilings, and panoramic windows gives you sweeping views of the Pacific Ocean and is backdropped by the rugged Ko'olauloa Mountains. Sit ... Registration Number: 5901400900000, TA-104-468-6848-01, GE-104-468-6848-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Haleiwa
TAX ID #TA057-911-5008-02 GE#057-911-5008-02 Our guest area is a great place to come and work from our guest area (we have excellent internet connection and a relaxed computer station counter) we accept 30 nights or longer. For your safety and ... Registration Number: TA-057-911-5008-02, GE-057-911-5008-02, 590040550000
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Haleiwa
Enjoy this Lovely Tropical Paradise!! Registration Number: GE-096-482-7136-01, TA-096-482-7136-01 ********************************************************************************************************... Registration Number: TA-096-482-7136-01, GE-096-482-7136-01
5 bedroom
3 bathroom
Sleeps 10
Hawaii> Haleiwa
Welcome to Hale 4 U! Walk thru the front door to be greeted by a breathe taking panoramic view of the most unique coastline in the Hawaiian Islands. Nestled in a gated community that features a private bay, this beachfront 5 bedroom, 3-5 ... Registration Number: TA-070-626-9184-01, GE-070-626-9184-01
5 bedroom
4 bathroom
Sleeps 9
Hawaii> Haleiwa
Aloha,Registration Number: GE-010-526-5152-02, TA-010-526-5152-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Haleiwa
For more information please go to HonuPacificSurf.comRegistration Number: 16101201, TA-108-239-6672-01, GE-108-239-6672-01
4 bedroom
4 bathroom
Sleeps 12
Hawaii> Haleiwa
Inquire about available dates even if shown as blocked, as some dates are blocked for homeowners and they are flexible. Registration Number: 0040960000000, TA-020-234-2400-01, GE-020-234-2400-01
4 bedroom
6 bathroom
Sleeps 10
Hawaii> Haleiwa
This Cottage by the Sea is the perfect getaway for your Hawaiian Vacation. This 3 bedroom 2 bath completely remodeled home sparkles like New, in walking distance to the Best North Shore Beaches, Attractions, and Eateries. After a long day at the... Registration Number: TA-057-752-7808-01, 580050180003, GE-057-752-7808-01
3 bedroom
2 bathroom
Sleeps 7
Hawaii> Haleiwa
This amazing timber frame home provides privacy and luxury on the beach. A central courtyard garden creates a zen atmosphere while the fully appointed kitchen allows the creation of gourmet meals or just mai tais to enjoy overlooking beautiful ... Registration Number: 159003007000, GE-026-381-5168-01, TA-026-381-5168-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Haleiwa
Aloha, and welcome to paradise! Registration Number: TA-061-502-2592-01, GE-151-625-8304-01
7 bedroom
8 bathroom
Sleeps 14
Hawaii> Haleiwa
ALOHA is what Sunsetyards is all about. Partial ocean views. Registration Number: GE-151-548-3136-01, TA-151-548-3136-01
3 bedroom
1 bathroom
Sleeps 4
Hawaii> Haleiwa
Hale Malina is a luxury custom home is located in a quiet gated community directly beachfront on Sunset Beach, with miles of beautiful sandy beach and world class surf break right outside your door. Registration Number: GE-024-872-9600-01, TA-024-872-9600-01
4 bedroom
3 bathroom
Sleeps 12
Hawaii> Haleiwa
Sunset Beach Retreat is only a one block walk to the famous Sunset Beach and Backyards on the North Shore of Oahu. Registration Number: 590010040006, GE-131-721-4208-01, TA-131-721-4208-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Haleiwa
The unit has a TV with cable, internet connection, a full kitchen with a stove top, refrigerator, microwave, toaster, blender, coffee maker, plates, pans, and other Registration Number: GE-067-786-3424-01, TA-067-786-3424-01
3 bedroom
1 bathroom
Sleeps 5
Hawaii> Haleiwa
Paradise At Your DoorstepRegistration Number: TA-121-502-9248-01, GE-121-502-9248-01
3 bedroom
3 bathroom
Sleeps 7
Hawaii> Haleiwa
The studio has a TV with cable, internet connection, air conditioner, a full kitchen with an oven and stove, a refrigerator, microwave, toaster, blender, coffee maker, plates, pans, and other eating utensils. The studio is very clean, very ... Registration Number: GE-067-786-3424-01, 159003081000, TA-067-786-3424-01
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Haleiwa
Registration Number: 590020080000, TA-098-696-8064-01, GE-098-696-8064-01
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Haleiwa
Beachfront property on Northshore Oahu. Beautiful home on the beach with close access to numerous activities. Gated property with lots of privacy, beachside lanai and memorable sunsets. House has 3 Bedrooms and 2 full Bathrooms with ac in all ... Registration Number: 59020050, TA-036-160-3072-01, GE-036-160-3072-01
4 bedroom
3 bathroom
Sleeps 6
Hawaii> Haleiwa
