hide node and its subnode if condition is meet

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

hide node and its subnode if condition is meet

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-01-21 : 01:33:08

Hi

I have the following table...


USE [TestDB]
GO

/****** Object:  Table [dbo].[ProductsTree]    Script Date: 2014-01-21 07:22:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ProductsTree](
	[NodeID] [int] IDENTITY(1,1) NOT NULL,
	[ParentNodeID] [int] NULL,
	[Text] [nvarchar](50) NULL,
	[Active] [bit] NULL,
 CONSTRAINT [PK_ProductsTree] PRIMARY KEY CLUSTERED 
(
	[NodeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO



INSERT INTO ProductsTree (Text,ParentNodeID,Active) VALUES ('Folder 1', NULL, 1)
INSERT INTO ProductsTree (Text,ParentNodeID,Active) VALUES ('Item 1', 1, 1)
INSERT INTO ProductsTree (Text,ParentNodeID,Active) VALUES ('Item 2', 1, 1)

INSERT INTO ProductsTree (Text,ParentNodeID,Active) VALUES ('SubFolder 1', 1, 1)
INSERT INTO ProductsTree (Text,ParentNodeID,Active) VALUES ('Item 1', 4, 0)
INSERT INTO ProductsTree (Text,ParentNodeID,Active) VALUES ('Item 2', 4, 1)

INSERT INTO ProductsTree (Text,ParentNodeID,Active) VALUES ('SubFolder 2', 4, 1)
INSERT INTO ProductsTree (Text,ParentNodeID, Active) VALUES ('Item 3', 7, 0)
INSERT INTO ProductsTree (Text,ParentNodeID, Active) VALUES ('Item 4', 7, 0)

The table have a relationship between ParentNodeID and NodeID that is used to create a tree structure. I wonder if its possible to check and only display "folders" that have active nodes in them. For example the "Subfolder 2" only have inactive nodes in itself, therfore that whole subfolder and its nodes should not be displayed, and this should apply all the way meaning if there is further more subfolders that meet the same condition thoose should not be displayed either. Can this be achieved?

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-01-21 : 04:16:56

Like Following?

WITH Products (NodeID, ParentNodeID, Text, Active, LeafActive, IsLeaf) AS (
SELECT *, ProductsTree.Active, 1
FROM ProductsTree
WHERE NOT EXISTS (
	SELECT *
	FROM ProductsTree Children
	WHERE Children.ParentNodeID = ProductsTree.NodeID)
UNION ALL
SELECT ProductsTree.*, Children.LeafActive, 0
FROM ProductsTree
INNER JOIN Products Children
	ON Children.ParentNodeID = ProductsTree.NodeID
)
SELECT DISTINCT NodeID, ParentNodeID, Text, Active
FROM Products
WHERE LeafActive = 1
AND IsLeaf = 0

-------------------------------------
From Japan
Sorry, my English ability is limited.

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-01-21 : 04:24:57

Almost, the nodes that are Active and in a subfolder that is displayed should also be displayed. Right now the "folders" is only displayed...

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-01-22 : 07:36:43

Do you mean following?
-----------------------------------------------------------------

WITH Products (NodeID, ParentNodeID, Text, Active) AS (
SELECT *
FROM ProductsTree
WHERE NOT EXISTS (
	SELECT *
	FROM ProductsTree Children
	WHERE Children.ParentNodeID = ProductsTree.NodeID)
AND Active = 1
UNION ALL
SELECT ProductsTree.*
FROM ProductsTree
INNER JOIN Products Children
	ON Children.ParentNodeID = ProductsTree.NodeID
)
SELECT DISTINCT NodeID, ParentNodeID, Text, Active
FROM Products

-------------------------------------
From Japan
Sorry, my English ability is limited.

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-01-22 : 07:53:32

That is just what I meant, awsome!! Thanks a bunch!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources