Prev: Multiple Unions and functions
Next: Restore database using SMO
From: Max on 31 Mar 2010 02:43

Hi all,

I'm not an expert in SQL and I need a little help. I'm developing a php
script working with a tree stored in database. Looking in Internet I
found this article showing a good method for manage a tree using the
nested set way:

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

The queries showed cover quite all my needs but there only one
information I need to know not reported. Starting from the following
query used to have the list of immediate subordinates of a node:

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;

+----------------------+-------+
| name | depth |
+----------------------+-------+
| MP3 PLAYERS | 1 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+-------+

I need a similar query for have ONLY the number of the immediate
subordinates of a node. Following the proposed example the result of a
query I need should be 3 (this node have three direct childs nodes).
Someone can show me the right query for obtain this value?
Thank you very much the the help

--


--

From: Plamen Ratchev on 31 Mar 2010 09:24
Seems the syntax of the query is MySQL as this will not work on SQL Server. The solution to your problem should be very
simple, just use the whole query as derived table and get the count (you do not need ORDER BY in the query):

SELECT COUNT(*) AS count_subordinates
FROM (
<your query>
) AS T;

--
Plamen Ratchev
http://www.SQLStudio.com
From: Max on 31 Mar 2010 10:26
Plamen Ratchev wrote:

> Seems the syntax of the query is MySQL as this will not work on SQL
> Server. The solution to your problem should be very simple, just use
> the whole query as derived table and get the count (you do not need
> ORDER BY in the query):
>
> SELECT COUNT(*) AS count_subordinates
> FROM (
> <your query>
> ) AS T;

Simply and working :-)
Thank you very much

--

 | 
Pages: 1
Prev: Multiple Unions and functions
Next: Restore database using SMO