Darren Duncan wrote: > At 8:12 PM +0200 8/3/04, Rolf Schaeuble wrote: >>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
Thanks for your answer. When trying the first of your suggestions, the result I got was this: +-----------+-------------+-------+-------+ | object_id | value_table | value | value | +-----------+-------------+-------+-------+ | 1 | 1 | Hallo | 123 | | 1 | 2 | Hallo | 123 | +-----------+-------------+-------+-------+ The problem here is that in both rows of the result, both the string_value and the int_value are present. I get this result both in SQLite and in MySQL. I don't really understand why this happens; when the filtering is done in the JOIN, at least MySQL produces the result that I want. When I add more int-properties, it gets even funnier: The result doesn't change. The new properties don't appear, I just get the same result that I got before inserting them; with my original select in MySQL they would appear. To try it, you can use the following SQL statements: INSERT INTO properties VALUES (1, 101, 2); INSERT INTO int_values VALUES (101, 111); INSERT INTO properties VALUES (1, 102, 2); INSERT INTO int_values VALUES (102, 222); INSERT INTO properties VALUES (1, 103, 2); INSERT INTO int_values VALUES (103, 333); Any more ideas on how to get the result I want from SQLite? Or is this something that can't be done? Thanks again. Rolf Sch�uble
