Hi,
I have a strange behaviour of SQLITE with a HAVING-Clause and I don't
find the problem. Possibly a bug in SQLITE?
Following Query regarding a nested tree set to get all children of a node:
SELECT node.id,node.lft, node.rgt, (COUNT(parent.id) - (sub_tree.depth +
1)) AS depth FROM target_directory AS node, target_directory AS parent,
target_directory AS sub_parent, (SELECT node.id, (COUNT(parent.id) - 1)
AS depth FROM target_directory AS node, target_directory AS parent WHERE
node.lft BETWEEN parent.lft AND parent.rgt AND node.id = 1 GROUP BY
node.id ORDER BY node.lft )AS sub_tree WHERE node.lft BETWEEN parent.lft
AND parent.rgt AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.id = sub_tree.id GROUP BY node.id HAVING depth***<=* 1
ORDER BY node.lft ;
See the pic1_working.PNG for the result with the given Database.
Problem: The result contains the parent-node also. So I changed last
statement "HAVING depth <= 1" to "HAVING depth<1" and the result of the
query is now empty (see pic2_not_working.png) -- expected would be one
result set.I
Sorry to ask that silly question: problem "sitting in front of keyboard"
or in SQLITE?
Thanks and Best Regards,
Tobias
SQLITE-Version is 3.7.17
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
