SELECT SQL_CALC_FOUND_ROWS wp_posts.*, (
SELECT SUM(rooms_available) rooms_available FROM (SELECT IFNULL(SUM(room_count), 0) rooms_available, availabilities.accommodation_id FROM wp_byt_accommodation_vacancies availabilities INNER JOIN (
select d.the_date from
(
select adddate('2024-04-25',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) the_date from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
having the_date between '2024-04-25' and '2026-04-25'
ORDER BY the_date
) d
where DATE(d.the_date) >= DATE(CURDATE())
group by d.the_date
order by d.the_date
) possible_dates ON (possible_dates.the_date >= DATE(availabilities.start_date) AND possible_dates.the_date < DATE(availabilities.end_date)) WHERE 1=1 AND ('2024-04-25' BETWEEN start_date AND end_date OR start_date <= '2026-04-25') AND end_date >= '2024-04-25' GROUP BY possible_dates.the_date, availabilities.accommodation_id ) as ra WHERE (accommodation_id = wpml_translations_default.element_id OR accommodation_id = wpml_translations.element_id) ) rooms_available , (
SELECT IFNULL(SUM(rooms_booked), 0) rooms_booked FROM ((SELECT IFNULL(SUM(bookings.room_count), 0) rooms_booked, bookings.accommodation_id, possible_dates.the_date FROM wp_byt_accommodation_bookings bookings INNER JOIN (
select d.the_date from
(
select adddate('2024-04-25',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) the_date from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
having the_date between '2024-04-25' and '2026-04-25'
ORDER BY the_date
) d
where DATE(d.the_date) >= DATE(CURDATE())
group by d.the_date
order by d.the_date
) possible_dates ON (possible_dates.the_date >= DATE(bookings.date_from) AND possible_dates.the_date < DATE(bookings.date_to)) WHERE 1=1 GROUP BY possible_dates.the_date, accommodation_id ) as rb, (SELECT MIN(start_date) min_start_date, MAX(end_date) max_end_date, accommodation_id FROM wp_byt_accommodation_vacancies GROUP BY accommodation_id) min_max_available_dates ) WHERE (rb.accommodation_id = wpml_translations_default.element_id AND min_max_available_dates.accommodation_id = wpml_translations_default.element_id) OR (rb.accommodation_id = wpml_translations.element_id AND min_max_available_dates.accommodation_id = wpml_translations.element_id) AND rb.the_date >= min_max_available_dates.min_start_date AND rb.the_date < min_max_available_dates.max_end_date ) rooms_booked , IFNULL((SELECT price_meta2.meta_value + 0 FROM wp_postmeta price_meta2 WHERE price_meta2.post_id=wpml_translations_default.element_id AND price_meta2.meta_key='_accommodation_min_price:2024-04-25:2026-04-25' LIMIT 1), 0) accommodation_price , IFNULL((SELECT price_meta3.meta_value + 0 FROM wp_postmeta price_meta3 WHERE price_meta3.post_id=wpml_translations_default.element_id AND price_meta3.meta_key='accommodation_static_from_price' LIMIT 1), 0) accommodation_static_price
FROM wp_posts JOIN wp_icl_translations wpml_translations
ON wp_posts.ID = wpml_translations.element_id
AND wpml_translations.element_type = CONCAT('post_', wp_posts.post_type) LEFT JOIN wp_icl_translations wpml_translations_default ON wpml_translations_default.trid = wpml_translations.trid AND (wpml_translations_default.source_language_code IS NULL OR wpml_translations.source_language_code IS NULL)
WHERE 1=1 AND wp_posts.post_type = 'accommodation' AND ((wp_posts.post_status = 'publish')) AND ( ( ( wpml_translations.language_code = 'en' OR 0 ) AND wp_posts.post_type IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_global_styles','wp_navigation','location','review','extra_item','room_type','accommodation','tour','cabin_type','cruise','car_rental','wpcf7_contact_form','cookielawinfo' ) ) OR wp_posts.post_type NOT IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_global_styles','wp_navigation','location','review','extra_item','room_type','accommodation','tour','cabin_type','cruise','car_rental','wpcf7_contact_form','cookielawinfo' ) )
GROUP BY wp_posts.ID HAVING 1=1 AND rooms_available > rooms_booked AND rooms_available >= 1
ORDER BY accommodation_price DESC
LIMIT 0, 12
Set in a slightly elevated position, just 200m from Kouremenos Beach, these 3 villas are surrounded by a large garden with lawns and olive trees and enjoy wonderful, uninterrupted views of the countryside and down to the sea (villa C’ has part sea-view).
The breathtaking views over the bay of Sitia, the high quality furnishing, great attention to detail and 2 extremely inviting swimming pools make this complex very special and most impressive! Roussa Village, built in 2009, is well-situated on the side of a hill on a quiet road from Sitia to the village of Roussa Eklissia.
These three villas, built in traditional Cretan style, are situated on the slope of a hill, in a very quiet and peaceful area, with a spectacular view of the long, sand and pebble beach of Kouremenos and the surrounding mountains.
A cluster of comfortable apartments set in the unspoilt Cretan countryside and 3.000 sq.m of lush green olive grove, approx. 70 metres from the sand and pebble beach of Kouremenos, Grandes Apts (named after the small islands of Kouremenos Bay), are the perfect choice for those who want to relax and enjoy the sea and sun in peaceful surroundings.
Nestling quietly on the hillside, with outstanding, uninterrupted 180° views over the bay of Sitia and with the most spectacular sunsets, you will find this unique property, an ideal choice for couples seeking peace, relaxation and complete privacy.
Hidden in a quiet lush olive grove on the outskirts of the small traditional village of Agathias, approx. 1400 metres from the village of Palekastro and approx. 1200 metres from the sand and pebble beach of Chiona and the small traditional fish tavernas, Aeolos Studios are ideal for those wishing for peaceful and restful holidays in the Cretan countryside.
Set on a hillside, enjoying an uninterrupted view over the magnificent bay of Kouremenos, as well as the lush olive groves that fringe the shore, these apartments are approx. 1400 metres from the long, sand and pebble beach of Kouremenos, and approx. 3 km from the village of Palekastro.
Situated just above the small fishing harbour of Sitia, approx. 160 metres from the town centre and approx. 120 metres from the sand and pebble beach of Sitia, this family owned and run hotel is composed of two buildings, one newly built and the other newly refurbished, both in a modern /classic style.
Almost on the water’s edge, this small family complex overlooks the sea and the countryside. It is situated on the edge of the town of Sitia, separated from the sandy beach by a pedestrian area. All apartments and studios offer spacious and comfortable accommodation, the interior design reflecting the soft colours of the Aegean. Some have sea view and some garden view.
On a hillside in a quiet, peaceful position just outside the small traditional village of Agathias, the newly-built Elia Studios enjoy panoramic views over the silver-green olive groves and the golden shores of Chiona and Kouremenos bays, with their sand and pebble beaches.There is a very inviting swimming pool (4 x 7,5 x 1,80m).
This most attractive complex is built on two floors and is surrounded by impressive gardens, complete with a delightful waterfall feature. It is separated from the sandy beach of Sitia by the Sitia-Palekastro road and is a mere 5 minute walk to the town centre.
Cookies are small files which are sent to your browser and stored on your computer’s hard disc. If you have registered with Portobelis Crete Holidays, then your computer will store an identifying code in the cookie which means you do not need to type in your email address each time you return to the site. Other than this, we do not store your password or any other information in the cookie, or use it for any other purpose. Our third-party advertiser may place or recognize a unique cookie on your browser.
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.