Harry Beezhold wrote: > Sqlite - What a cool product! Do you really think buggy products are cool? ;-)
> The following is a description of an apparent bug in > the calculation of a row count of a left joined table. > The leftjoin/count technique seems to work for each join/count, separately. > However, if done together, the counts appear to have been multiplied I created a simplified example: CREATE TABLE persons(personID); INSERT INTO "persons" VALUES('I'); CREATE TABLE parentschildren(parentID, childID); INSERT INTO "parentschildren" VALUES('father','I'); INSERT INTO "parentschildren" VALUES('mother','I'); INSERT INTO "parentschildren" VALUES('I','son'); INSERT INTO "parentschildren" VALUES('I','daughter'); A single join works as expected: SELECT personID, parents.parentID FROM persons LEFT JOIN parentschildren AS parents ON personID = parents.childID; personID parentID ---------- ---------- I father I mother If you join that with another table, you get more records because _each_ "I" record matches two children records: SELECT personID, parents.parentID, children.childID FROM persons LEFT JOIN parentschildren AS parents ON personID = parents.childID LEFT JOIN parentschildren AS children ON personID = children.parentID; personID parentID childID ---------- ---------- ---------- I father daughter I father son I mother daughter I mother son This is how SQL joins work. If you want to get _independent_ parent and children counts, you should use correlated subqueries instead: SELECT personID, (SELECT COUNT(*) FROM parentschildren WHERE childID = personID) AS parentCount, (SELECT COUNT(*) FROM parentschildren WHERE parentID = personID) AS childCount FROM persons; personID parentCount childCount ---------- ----------- ---------- I 2 2 Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users