On 22 Dec 2013, at 11:12pm, Tobias Steinmann <tobias.steinm...@gmx.de> wrote:
> 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 ; Argh. An example where doing the work in your software rather than a huge SQL statement might be more understandable, both in your debugging and if anyone else ever has to read your code. Another way to simplify things might be to define your sub-select as a VIEW. General note: when making up a name for a calculation like 'depth', try to make sure it's not the name of any of the columns in the tables mentioned in your SELECT. This avoids ambiguity. > See the pic1_working.PNG for the result with the given Database. Sorry, attachments don't work on this list. We don't want everyone sending us their homework. > 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. Nothing obvious but try these: (A) remove the ORDER BY clauses and everything else you can think of and see whether you still get weird results. See if you can make the simplest possible SELECT that comes up with unexpected results. See if you can get weird results by substituting a specific value for the sub-SELECT. The smaller the SELECT the easier it is to see what's wrong. (B) check to see if you have NULLs anywhere. They tend to make things look weird. Hope some of that helps. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
