Costa Rica VacationsCosta Rica Vacations
Forgot password?

Search Accommodation Results for:

CREATE TEMPORARY TABLE IF NOT EXISTS Search_jkjde8g4te46h53vghd3fcacm2 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_posts AS post_s1 
						WHERE (post_s1.post_status = 'publish') AND (post_s1.post_type = 'accommodation')) AS t0
						INNER JOIN crv_icl_translations it1 ON (it1.element_type = 'post_accommodation') AND it1.element_id = t0.acc_id
						INNER JOIN crv_icl_translations it2 ON (it2.element_type = 'post_accommodation') AND it2.language_code='en' AND it2.trid = it1.trid  ) AS t1
					INNER JOIN crv_postmeta AS meta_c1 ON (meta_c1.meta_key = 'trav_room_accommodation') AND (t1.acc_id = meta_c1.meta_value)
					INNER JOIN crv_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_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_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_jkjde8g4te46h53vghd3fcacm2 as t1
				INNER JOIN crv_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_postmeta AS meta_rating ON (t1.acc_id = meta_rating.post_id) AND (meta_rating.meta_key = 'review')
				LEFT JOIN crv_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 review DESC LIMIT 0, 15;

Sort results by:

doubletree resort all inclusive

Fiesta All Inclusive Resort
Puntarenas Costa Rica

1 reviews

The DoubleTree Resort is part of the renowned hotel chain, the Hilton. Located in Puntarenas along the Pacific Coast of

avg/night$125.00 SELECT
Aguila de Osa Beautiful rustic bedroom

Aguila de Osa Inn
Osa Peninsula Costa Rica

1 reviews

Situated in one of the most bio-diverse and intense places on the planet (according to National Geographic) this boutique hotel

avg/night$611.00 SELECT
Looking for a hotel with ocean view? Hotel Tamarindo Vista Villas
0 reviews

Tamarindo Vista Villas in Tamarindo, Guanacaste is part of the hotel chain: Best Western. Across the road from Tamarindo Beach

avg/night$75.00 SELECT
Arenal Paraiso Romantic

Arenal Paraiso Hotel & Spa
Alajuela Costa Rica

0 reviews

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

avg/night$99.00 SELECT
Costa Rica Marriott

Marriott Costa Rica San Jose
San Jose Costa Rica

0 reviews

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

avg/night$223.00 SELECT
Romance parador hotel

Parador Hotel
Manuel Antonio Costa Rica

0 reviews

A “parador” is a hotel with a European style to their design and the Parador Hotel in Manuel Antonio is

avg/night$250.00 SELECT
laguna lodge pool

Laguna Lodge
Tortuguero Costa Rica

0 reviews

Laguna Lodge is an eco-tourism hotel located in Tortuguero. While it sits on fifteen acres of trees and tropical plants,

avg/night$223.00 SELECT
mimos hotel

Mimos Hotel
Manuel Antonio Costa Rica

0 reviews

Mimos Hotel is in Manuel Antonio, Costa Rica’s hot-spot for tourism, surfers and nature lovers. With a famous natural reserve

avg/night$75.00 SELECT
El rodeo country inn in Santa Ana

El Rodeo Boutique Hotel
Santa Ana Costa Rica

0 reviews

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

avg/night$115.00 SELECT
Grano de Oro

Grano de Oro
San Jose Costa Rica

0 reviews

In the heart of San Jose, Grano de Oro is a boutique hotel with luxury rooms that converts a Victorian

avg/night$225.00 SELECT
villa caletas

Villa Caletas
Jaco Costa Rica

0 reviews

Villa Caletas is a luxury boutique hotel located on a rainforest hilltop in Jaco with a Tropical Victorian Architecture that

avg/night$190.00 SELECT
Tortuga Lodge

Tortuga Lodge
Tortuguero Costa Rica

0 reviews

Tortuga Lodge is a boutique hotel in Tortuguero on the north-Caribbean side of Costa Rica. This hotel is close to

avg/night$618.00 SELECT
Punta Islita Pool

Punta Islita Resort
Punta Islita Costa Rica

0 reviews

Perched above a secluded cove along the Pacific Coast of Guanacaste, sit one of the finest eco-friendly luxury hotels; Punta

avg/night$255.00 SELECT
Poco a Poco in Monteverde

Poco a Poco Hotel
Monteverde Costa Rica

0 reviews

A few minutes from one of Costa Rica’s most famous cloud forest reserve, Monteverde, sits Poco a Poco Hotel &

avg/night$165.00 SELECT
Arenas del Mar pool side

Arenas del Mar Hotel
Manuel Antonio Costa Rica

0 reviews

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

avg/night$400.00 SELECT