SELECT node.nid as nid, node.title as ptitle , min(content_type_property.field_property_ltrental_min_amount) as NR , min(content_type_property.field_property_ltrental_max_amount) as NR_max, content_type_property.field_rent_request_value as POR FROM node inner join term_node tn on (tn.nid=node.nid) inner join term_data td on (tn.tid=td.tid) INNER JOIN term_node newlist_tn ON node.nid = newlist_tn.nid INNER JOIN term_data newlist_td ON newlist_td.tid = newlist_tn.tid LEFT JOIN content_field_r_property RP ON node.nid = RP.field_r_property_nid LEFT JOIN content_type_property ON node.vid = content_type_property.vid WHERE node.status <> 0 AND node.type = 'property' and (td.vid=1) and (td.tid = 2) AND (newlist_td.tid = 496) GROUP BY node.nid ORDER BY POR ASC, NR ASC, NR_max ASC
42 Properties found

Villas 10 - 19 of 42

Show:
Clerview Heights, Stage 1 #48

This Clerview Heights property is a fully furnished single storey, 3 bed 2 bathroom house for rent, in the established residential neighbourhood of Clermont in St James/Michael boarders close to ...

3 2
Clermont Green 24

Clermont Green is a prime residential development consisting of 36 townhouses. Number 24 like all the other units have 2 bedroom and 1.5 bathroom. This townhouse has high quality fittings and finishes...

2     1.5
Clermont Gardens, Lot D, First Floor Apt,

Clermont Gardens lot D is a large two storey building comprising of TWO x THREE bed apartments with buit in garage.The property is is set in its own fenced in grounds with mature fruit trees and is lo...

3 2
Kendall Hill #1

Kendall Hill #1 is a single storey unfurnished villa in a pleasant residential area. The villa sits on 6,100 Sqft of land with mature fruit trees and a consistantly cool easterly breeze. The property ...

3     2
Miramar Villas #3

This spacious 2 bedroom 2 bathroom ground floor apartment is centrally located in Mount Standfast, St James. It has a large spacious living room featuring  a large TV, dining room ...

2 2
Miramar Villas #4

This spacious 2 bedroom 2 bathroom ground floor apartment is centrally located in Mount Standfast, St James. It has a large spacious living room featuring  a large TV, dining room ...

2     2
Westmoreland House

Large two storey house with 3 bedrooms, 2.5 bathrooms, garage in large landscaped gardens located on highway 2A near the junction at Royal Westmoreland and Apes Hill.Inside the house on the ground flo...

3 2.5
Vuemont Apartment 249.

Vuemont Apartment 249 is a modern two bedromm 1.5 bathroom property located on the ground floor overlooking Vueomont resort pools and views of the Caribbean sea.This apartment is well appointed, fully...

2     2
Clerview Heights, Stage 2 #16

This single storey 3 bed 2 bathroom house is for long term rent and is located in the established residential neighbourhood of Clermont, St Michael ans is walking distance to the Warrens development a...

3 2

Villas 10 - 19 of 42

Show: