LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Programming (https://www.linuxquestions.org/questions/programming-9/)
-   -   sql query; group by / having (https://www.linuxquestions.org/questions/programming-9/sql-query%3B-group-by-having-784032/)

h/w 01-22-2010 09:34 AM
sql query; group by / having
 
Hello,
I'm trying out the examples given here using nested sets for dealing with hierarchical data in databases: http://dev.mysql.com/tech-resources/...ical-data.html

My question's regarding the example in the section 'Find the Immediate Subordinates of a Node', which provides the following query:

Code:
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
        nested_category AS parent,
        nested_category AS sub_parent,
        (
                SELECT node.name, (COUNT(parent.name) - 1) AS depth
                FROM nested_category AS node,
                nested_category AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                AND node.name = 'PORTABLE ELECTRONICS'
                GROUP BY node.name
                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.name = sub_tree.name
GROUP BY node.name
HAVING depth <= 1
ORDER BY node.lft;
I'm using sqlite3 for my testing, and on running the above query, my results are:
Code:
+----------------------+-------+
| name                | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS |    0 |
| MP3 PLAYERS          |    1 |
| Flash                |    2 |
| CD PLAYERS          |    1 |
| 2 WAY RADIOS        |    1 |
+----------------------+-------+
instead of the one given in the example, viz.:
Code:
+----------------------+-------+
| name                | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS |    0 |
| MP3 PLAYERS          |    1 |
| CD PLAYERS          |    1 |
| 2 WAY RADIOS        |    1 |
+----------------------+-------+
Can someone please explain why the having expression does not work, and instead of only selecting the node.name's that have depth <= 1, selects them all (as seen from 'Flash 2')?

Thanks in advance.

h/w 01-22-2010 04:39 PM
Nevermind, this was brought up elsewhere - http://stackoverflow.com/questions/8...ry-not-working

The working query is:
Code:
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS dpth
FROM nested_category AS node,
        nested_category AS parent,
        nested_category AS sub_parent,
        (
                SELECT node.name, (COUNT(parent.name) - 1) AS depth
                FROM nested_category AS node,
                nested_category AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                AND node.name = 'PORTABLE ELECTRONICS'
                GROUP BY node.name
                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.name = sub_tree.name
GROUP BY node.name
HAVING dpth <= 1
ORDER BY node.lft;

h/w 01-25-2010 12:12 AM
Not being a DBA, I realize this is far from optimal for large datasets. Any suggestions on how to make it so?


All times are GMT -5. The time now is 08:57 PM.