SELECT object.object_id, properties.value_table, string_values.value, int_values.value
FROM object LEFT OUTER JOIN properties ON object.object_id = properties.object_id LEFT OUTER JOIN string_values ON (properties.value_table = 1) AND (properties.property_id = string_values.property_id)
LEFT OUTER JOIN int_values ON (properties.value_table = 2) AND (properties.property_id = int_values.property_id) ;
Your original query looks mal-formed. Join clauses are meant to be used only for saying what the related columns are, and not any filtering conditions. You do filtering in a WHERE clause. Like this:
SELECT
object.object_id,
properties.value_table,
string_values.value,
int_values.value
FROM object
LEFT OUTER JOIN properties ON object.object_id = properties.object_id
LEFT OUTER JOIN string_values ON properties.property_id = string_values.property_id
LEFT OUTER JOIN int_values ON properties.property_id = int_values.property_id
WHERE
(properties.table_value = 1 AND string_values.property_id IS NOT NULL) OR
(properties.table_value = 2 AND int_values.property_id IS NOT NULL)
;
Even then, I think that your value_table field serves no purpose at all, and you can just do this, assuming that string_values and int_values records will not exist when they shouldn't:
SELECT
object.object_id,
string_values.value,
int_values.value
FROM object
LEFT OUTER JOIN properties ON object.object_id = properties.object_id
LEFT OUTER JOIN string_values ON properties.property_id = string_values.property_id
LEFT OUTER JOIN int_values ON properties.property_id = int_values.property_id
;
Moreover, the above query assumes you can have objects without any properties (which is valid). However, if all objects must have at least one property, then you can remove the object table from the query, as it doesn't add anything then.
-- Darren Duncan
