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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
