Re: [sqlite] LEFT JOIN doesn't work as expected
Right. I'm sorry. I should get rid of this CC'ing habit ;-) Rolf D. Richard Hipp wrote: [EMAIL PROTECTED] wrote: P.S.: I have put Mr. Hipp on CC because after many hours of testing and reading up on SQL (that's basically all I did today), I'm quite convinced that there's a bug. I read the mailing list
Re: [sqlite] LEFT JOIN doesn't work as expected
[EMAIL PROTECTED] wrote: P.S.: I have put Mr. Hipp on CC because after many hours of testing and reading up on SQL (that's basically all I did today), I'm quite convinced that there's a bug. I read the mailing list -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: Re: [sqlite] LEFT JOIN doesn't work as expected
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
Re: [sqlite] LEFT JOIN doesn't work as expected
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
Re: [sqlite] LEFT JOIN doesn't work as expected
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
