Costa Rica Vacations EspañolCosta Rica Vacations Español
Forgot password?

Search Accommodation Results for: 

CREATE TEMPORARY TABLE IF NOT EXISTS Search_fb378c62ea5954331faf1c2d8dc5c2b5 AS SELECT t1.*, meta_c1.post_id AS room_id, meta_c2.meta_value AS max_adults, meta_c3.meta_value AS max_kids, meta_c4.meta_value AS minimum_stay
					FROM ( SELECT DISTINCT it2.element_id AS acc_id FROM (SELECT post_s1.ID AS acc_id FROM crv_2_posts AS post_s1 
						WHERE (post_s1.post_status = 'publish') AND (post_s1.post_type = 'accommodation')) AS t0
						INNER JOIN crv_2_icl_translations it1 ON (it1.element_type = 'post_accommodation') AND it1.element_id = t0.acc_id
						INNER JOIN crv_2_icl_translations it2 ON (it2.element_type = 'post_accommodation') AND it2.language_code='es' AND it2.trid = it1.trid  ) AS t1
					INNER JOIN crv_2_postmeta AS meta_c1 ON (meta_c1.meta_key = 'trav_room_accommodation') AND (t1.acc_id = meta_c1.meta_value)
					INNER JOIN crv_2_postmeta AS meta_c2 ON (meta_c1.post_id = meta_c2.post_id) AND (meta_c2.meta_key='trav_room_max_adults')
					LEFT JOIN crv_2_postmeta AS meta_c3 ON (meta_c1.post_id = meta_c3.post_id) AND (meta_c3.meta_key='trav_room_max_kids')
					LEFT JOIN crv_2_postmeta AS meta_c4 ON (t1.acc_id = meta_c4.post_id) AND (meta_c4.meta_key='trav_accommodation_minimum_stay') WHERE (meta_c2.meta_value >= 1) AND (meta_c2.meta_value + IFNULL(meta_c3.meta_value,0) >= 1 + 0) GROUP BY acc_id
SELECT t1.*, post_l1.post_title as acc_title, meta_rating.meta_value as review, meta_price.meta_value as avg_price FROM Search_fb378c62ea5954331faf1c2d8dc5c2b5 as t1
				INNER JOIN crv_2_posts post_l1 ON (t1.acc_id = post_l1.ID) AND (post_l1.post_status = 'publish') AND (post_l1.post_type = 'accommodation')
				LEFT JOIN crv_2_postmeta AS meta_rating ON (t1.acc_id = meta_rating.post_id) AND (meta_rating.meta_key = 'review')
				LEFT JOIN crv_2_postmeta AS meta_price ON (t1.acc_id = meta_price.post_id) AND (meta_price.meta_key = 'trav_accommodation_avg_price') WHERE  1=1 GROUP BY acc_id ORDER BY acc_title ASC LIMIT 0, 15;

37 results found.

Modify Search


All

Sort results by:

Aguila de Osa Beautiful rustic bedroom

Ubicado en uno de los lugares con/de mayor biodiversidad del planeta (según el National Geographic), este “Boutique Hotel” ofrece a

avg/night442.00 SELECT
arenal kioro
reviews

Arenal Kioro sits on 27 acres of undulating land viewing the Arenal Volcano. With nature paths and thermal hot springs

avg/night375.00 SELECT
Arenal Nayara
reviews

Arenal Nayara is a luxury boutique hotel, widely recognized for it’s premium amenities and ultra-personalized service. Located in the outskirts

avg/night230.00 SELECT
Arenal Paraiso Romantic

Arenal Paraiso is a hotel and spa located in Alajuela that views the Arenal Volcano (one of the top must-see

avg/night85.00 SELECT
Arenas del Mar pool side

Arenas del Mar is located a few minutes away from Manuel Antonio National Park, an extraordinary place full of tropical

avg/night270.00 SELECT

Asclepios is a Hotel & Spa dedicated to the Greek God of Health who was known for his gift to

avg/night260.00 SELECT
Bosque de paz lodge

Bosque de Paz Lodge is constructed on thousand hectares of private reserve located in Alajuela, Costa Rica. This reserve has

avg/night115.00 SELECT
bougainvillea

Bougainvillea is an eco-friendly resort in Heredia, ten minutes from Costa Rica’s capital city. Close to the Airport and all

avg/night99.00 SELECT
Buena vista hotel

Buena Vista Hotel is located between Costa Rica’s most active volcano (Arenal) , the countries highest volcano (Irazu) and the

avg/night94.00 SELECT
Club del Mar in Jaco
reviews

Club del Mar is a resort and condominium positioned in Jaco, a popular beach town on Costa Rica`s Pacific Coast.

avg/night115.00 SELECT
Costa Rica Marriott

Costa Rica Marriott is part of a renowned luxury hotel chain. Located in La Ribera de Belen in Heredia, just

avg/night139.00 SELECT
el establo

Sitting in Monteverde (one of the most bio-diverse places in the world), El Establo is a four-star hotel that combines

avg/night215.00 SELECT
El rodeo country inn in Santa Ana

EL Rodeo Country Inn is a boutique hotel situated in Santa Ana, Heredia. For those flying from or to the

avg/night115.00 SELECT
el silencio

El Silencio Lodge & Spa is a luxury hotel located in Costa Rica’s Central Volcanic Valley viewing tropical cloud forest

avg/night295.00 SELECT