SELECT DISTINCT(p.propid),r.revs, p.reviews,if(p.reviews!="",p.reviews,r.revs) as newrev,p.category_id,category.name as cat_name,p.people_sleep,p.bedrooms,p.bathrooms,p.children,p.airport,p.features,p.pets,p.smoke,p.zip,
p.wheelchair,p.prop_title,p.prop_desc,p.property_type,prop_date,display_calender,other_url_calender,is_feature,p.isilink_coid, p.isilink_prop_id, p.isilink, p.escapia, p.escapia_prop_id, p.min_rate, p.max_rate,p.min_week, p.max_week,p.min_month, p.max_month, p.review_rating, s.offer_start_date as special
FROM property p
LEFT JOIN
(
SELECT propid,count(id) revs
FROM review
WHERE status = 1
GROUP BY propid
) r
ON r.propid = p.propid
LEFT JOIN special_offer s ON ( p.propid = s.propid )
LEFT JOIN category ON (p.category_id=category.id)
LEFT JOIN effective_dates AS ed ON(ed.propId=p.propId)
WHERE p.active IN("yes","d") AND p.category_id IN (2360,9702,9711,2361,2362,2363,2364,2365,2366,2367,2368,2369,2370,2371,2372,2373,2374,2375,2376,2377,2378,2379,2380,2381,2382,2396,2397,2398,2399,2400,2401,2402,2403,2404,2405,2406,2407,2408,2409,2410,2411,2412,2413,2414,2415,2416,2417,2418,2420,2421,9699,9700,2383,2384,2385,2386,2387,2388,2389,2390,2391,2392,2393,2394,2395) AND ( p.bedrooms="1" ) AND p.is_feature >= SYSDATE()
Vacation Rental Homes in Adirondacks, New York, Page 2 of 1
