SELECT SQL_CALC_FOUND_ROWS ww96_posts.*, 1 rooms_available, 0 rooms_booked , IFNULL((SELECT price_meta2.meta_value + 0 FROM ww96_postmeta price_meta2 WHERE price_meta2.post_id=ww96_posts.ID AND price_meta2.meta_key='_accommodation_min_price:2025-04-30:2027-04-30' LIMIT 1), 0) accommodation_price , IFNULL((SELECT price_meta3.meta_value + 0 FROM ww96_postmeta price_meta3 WHERE price_meta3.post_id=ww96_posts.ID AND price_meta3.meta_key='accommodation_static_from_price' LIMIT 1), 0) accommodation_static_price
FROM ww96_posts LEFT JOIN ww96_term_relationships ON (ww96_posts.ID = ww96_term_relationships.object_id) INNER JOIN ww96_postmeta ON ( ww96_posts.ID = ww96_postmeta.post_id )
WHERE 1=1 AND (
ww96_term_relationships.term_taxonomy_id IN (5,34)
) AND (
( ww96_postmeta.meta_key = 'accommodation_location_post_id' AND ww96_postmeta.meta_value IN ('36') )
) AND ww96_posts.post_type = 'accommodation' AND ((ww96_posts.post_status = 'publish'))
GROUP BY ww96_posts.ID HAVING 1=1
ORDER BY ww96_posts.post_title ASC
LIMIT 0, 12
Fabulous beachfront villa in Playa de Las Americas - the most popular destination in Tenerife. Heated pool, jacuzzi, private garage, air condition and more
Beautiful villa with large garden where you can enjoy stunning views of Playa de Las Americas while swimming in a heated pool or having BBQ with friends and family.
