Hi,

I have 4 tables holding data on houses. Using a join in the query is straight forward enough for three of the tables.

Code:
SELECT 
jos_hp_properties.id AS prop_id, 
jos_hp_properties.name AS prop_name, 
jos_hp_prop_types.name AS prop_type,
price, intro_text, xpos, ypos, thumb
 
FROM jos_hp_properties

JOIN 
    jos_hp_prop_types 
ON 
    jos_hp_prop_types.id=jos_hp_properties.type

JOIN 
    jos_hp_photos 
ON 
    jos_hp_photos.property=jos_hp_properties.id

 WHERE agent=$dev_id
However, the fourth table 'jos_hp_properties2' contains multiple entries for each house. E.g. 'Type of heating', 'no. of rooms' and 'parking space'

Constructed as;
id | property | field | value

I would like to extract the 'no. of rooms' which I know has a 'field' of 18.

Am I close with?
Code:
SELECT 
jos_hp_properties.id AS prop_id, 
jos_hp_properties.name AS prop_name, 
jos_hp_prop_types.name AS prop_type,
jos_hp_properties2.value AS prop_value,
price, intro_text, xpos, ypos, thumb
 
FROM jos_hp_properties

JOIN 
    jos_hp_properties2 
ON 
    jos_hp_properties2.property=jos_hp_properties.id (WHERE field = 18)

JOIN 
    jos_hp_prop_types 
ON 
    jos_hp_prop_types.id=jos_hp_properties.type

JOIN 
    jos_hp_photos 
ON 
    jos_hp_photos.property=jos_hp_properties.id

 WHERE agent=$dev_id
I'd appreciate any guidance.