Exclude child nodes i Select

24 Mar 2006 2:01 PM

Senna

Hi

Have this code, http://code.doria.se/?c=21, that I found at CodeProject.com.
(http://www.codeproject.com/cs/database/Trees_in_SQL_databases.asp)

To the Node table I have added a IsActive column, that you can see is being
used in the last query.

select c.*
from Node n, Tree t, Node c
where n.NodeName = 'Volvo'
    and n.Id = t.ParentId
    and t.Id = c.Id
    and c.IsActive = 1

Result:
2    1    Volvo    1
4    2    740    1
7    2    Turbo    1

The problem is that I want the child nodes of a node that is IsActive = 0 to
be excluded from the result as well. In this case the childnode "Turbo"
shouldn't be included cause its parent "940" is not IsActive.

Does anyone have any solution to this?

Thanks.

24 Mar 2006 2:19 PM

Senna

Sorry, the Insert for "Turbo" should be:
insert into Node(ParentId, NodeName) values(5, 'Turbo')

Show quote

"Senna" wrote:

> Hi
>
> Have this code, http://code.doria.se/?c=21, that I found at CodeProject.com.
> (http://www.codeproject.com/cs/database/Trees_in_SQL_databases.asp)
>
> To the Node table I have added a IsActive column, that you can see is being
> used in the last query.
>
> select c.*
> from Node n, Tree t, Node c
> where n.NodeName = 'Volvo'
>     and n.Id = t.ParentId
>     and t.Id = c.Id
>     and c.IsActive = 1
>
> Result:
> 2    1    Volvo    1
> 4    2    740    1
> 7    2    Turbo    1
>
> The problem is that I want the child nodes of a node that is IsActive = 0 to
> be excluded from the result as well. In this case the childnode "Turbo"
> shouldn't be included cause its parent "940" is not IsActive.
>
> Does anyone have any solution to this?
>
> Thanks.

24 Mar 2006 3:56 PM

ML

Have a look at this example:
http://milambda.blogspot.com/2005/07/climbing-trees-is-for-monkeys.html

It may not be exactly what you need, but it could lead you in the right
direction.

ML

---
http://milambda.blogspot.com/