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 (`headline` like '%pupukea%') AND sleeps>=1 order by rating desc LIMIT 0, 30
Pupukea Highlands sits above Waimea Bay and the magnificent beaches that are referred to as the “7 mile miracle” that makes up the North Shore of Oahu. Waiamea Bay is the birthplace of big wave surfing. Pipeline possesses the reputation of ... Registration Number: TA-078-298-3168-01, GE-078-298-3168-02, 590220140000
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Haleiwa
Visit the North Shore of Oahu and experience “Country Living” on a secluded one acre estate above Waimea Bay. Situated at the top of the Pupukea highlands, your hiking and cycling activities will be enhanced by the cool mountain air and ... Registration Number: 590310020000, TA-078-298-3168-01, GE-078-298-3168-02
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Haleiwa
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.Registration Number: 15925100, 90/TVU-0382 1990/NUC-2107, TA-086-229-4016-01, GE-086-229-4016-01
3 bedroom
3 bathroom
Sleeps 10
Hawaii> Haleiwa
Visit the North Shore of Oahu and experience “Country Living” on a secluded one acre estate above Waimea Bay. Situated at the top of the Pupukea highlands, your hiking and cycling activities will be enhanced by the cool mountain air and lack... Registration Number: 590310020000, TA-078-298-3168-01, GE-078-298-3168-02
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Haleiwa
Pupukea Highlands sits above Waimea Bay and the magnificent beaches that are referred to as the “7 mile miracle” that makes up the North Shore of Oahu. Waiamea Bay is the birthplace of big wave surfing. Pipeline possesses the reputation of ... Registration Number: TA-078-298-3168-01, GE-078-298-3168-02, 590220140000
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Haleiwa
Pupukea Highlands sits above Waimea Bay and the magnificent beaches that are referred to as the “7 mile miracle” that makes up the North Shore of Oahu. Waiamea Bay is the birthplace of big wave surfing. Pipeline possesses the reputation of ... Registration Number: TA-078-298-3168-01, GE-078-298-3168-02, 590220140000
1 bedroom
1 bathroom
Sleeps 1
Hawaii> Haleiwa
Lovely, spacious 2 bedroom+loft and 2 bathroom condo on Fazio golf course. This is a ground floor entry unit. Tastefully furnished. Screened-in lanai. Window air conditioning in both bedrooms. WIFI. Smart TV in living room and master ... Registration Number: GE-100-978-4832-01, TA-100-978-4832-01, 570010290112
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kahuku
Lovely, spacious 2 bedroom+loft and 2 bathroom condo on Fazio golf course. This is a ground floor entry unit. Tastefully furnished. Screened-in lanai. Window air conditioning in both bedrooms. WIFI. Smart TV in living room and master ... Registration Number: GE-100-978-4832-01, TA-100-978-4832-01, 570010290112, 90/TVU-0246
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kahuku
