Klint Gore <[EMAIL PROTECTED]> schrieb am 04.08.2004, 10:30:00: > On Wed, 4 Aug 2004 08:48:01 +0200, wrote: > > Any more ideas on how to get the result I want from SQLite? > > Or is this something that can't be done? > > union the 2 sets of properties together. if you need to add objects > with no properties, it would probably be easier to union them on the > end. > > SELECT > object.object_id, > p1.value_table, > string_values.value as stringval, > null as intval > >FROM object > JOIN properties p1 ON p1.object_id = object.object_id > JOIN string_values ON string_values.property_id = p1.property_id > where p1.value_table = 1 > > UNION > > SELECT > object.object_id, > p1.value_table, > null as stringval, > int_values.value > >FROM object > JOIN properties p1 ON p1.object_id = object.object_id > JOIN int_values ON int_values.property_id = p1.property_id > where p1.value_table = 2; > > object.object_id p1.value_table stringval int_values.value > ---------------- -------------- ---------- ---------------- > 1 2 123 > 1 1 Hallo > > klint. >
Hello Klint. With SQLite, this does the trick. However, it seems that it only works because of the typelessness of SQLite. If I try this query with MySQL, the result looks like this: +-----------+-------------+-----------+--------+ | object_id | value_table | stringval | intval | +-----------+-------------+-----------+--------+ | 1 | 2 | NULL | 123 | | 1 | 1 | | NULL | +-----------+-------------+-----------+--------+ For the stringval column, NULL is the first value. This value defines the type for this column. When later "Hallo" is added, it doesn't fit the type of the column and is somehow converted. It then appears as empty string. As I do not want to rely on behaviour specific to SQLite, I can't use this trick. Do you have an idea on how to make this work with a 'typed' database? Thanks Rolf Schäuble
