Hide node and its sub nodes - SQL Server Forums

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 sub nodes

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-05-15 : 06:26:34

Hi

I have the following table ..


USE [TestDB]
GO

/****** Object:  Table [dbo].[Products]    Script Date: 2013-05-15 10:50:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Products](
	[NodeID] [int] IDENTITY(1,1) NOT NULL,
	[ParentNodeID] [int] NULL,
	[Text] [nvarchar](50) NULL,
	[ShowItem] [bit] NULL,
	[IsEnabled] [bit] NULL,
	[CustID] [int] NULL,
	[Active] [nchar](10) NULL,
 CONSTRAINT [PK_Products] 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



USE [TestDB]
GO
SET IDENTITY_INSERT [dbo].[Products] ON 

GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (1, NULL, N'Root Folder 1', NULL, NULL, 1, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (2, NULL, N'Root Folder  2', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (3, 1, N'Book 1', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (4, 2, N'Book 2', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (5, 2, N'Book 3', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (6, NULL, N'Root Folder  3', NULL, 1, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (7, 6, N'Book 4', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (8, 6, N'Book 5', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (9, 6, N'Sub Folder 1', NULL, 1, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (10, 9, N'Book 6', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (11, 9, N'Book 7', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (12, 9, N'Book 8', NULL, NULL, NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[Products] OFF
GO

And I select data based on this Query..


SELECT     NodeId, ParentNodeId, Text, Active, CustID
FROM         dbo.Products
WHERE     (CustID = @CustID) AND (Active = 1)
   AND (ShowItem = 1 OR
                      ShowItem IS NULL)

ORDER BY [Text]

There is a relationsship between the ParentNodeID and NodeID that basically means that any parentNodeID is placed in a "subfolder" to its NodeID which build up a tree structure. My question is, if a NodeID have "IsHidden" = True, can I then create a Query that does not include that node and all NodeID's that is placed under that Node (its ParentNodeID = NodeID)?

Hope this make sence.....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-15 : 06:40:19

Sounds like this to me


;With CTE
AS
(
SELECT     NodeId, ParentNodeId, Text, Active, CustID,ShowItem,CAST(NodeId AS Varchar(max)) AS [Path]
FROM         dbo.Products p
WHERE     (CustID = @CustID) AND (Active = 1)
AND ParentNodeID IS NULL

UNION ALL

SELECT     p.NodeId, p.ParentNodeId, p.Text, p.Active, p.CustID,p.ShowItem,CAST(c.[Path] + '/' + CAST(p.NodeId AS varchar(10)) AS Varchar(max)) 
FROM         dbo.Products p
INNER JOIN CTE c
ON c.NodeId = p.ParentNodeId
WHERE     (CustID = @CustID) AND (Active = 1)
)

SELECT *
FROM CTE c
WHERE ShowItem=1
AND NOT EXISTS (SELECT 1 
               FROM CTE
               WHERE '/' + Path + '/' LIKE '%/' + CAST(c.NodeID AS varchar(10)) + '/%'
               AND ShowItem = 0
               )
ORDER BY [Text]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2013-05-15 : 06:51:13

Here's one way -- probably not the best way. Using recursion:



USE Tempdb
GO

/****** Object:  Table [dbo].[Products]    Script Date: 2013-05-15 10:50:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DROP TABLE [dbo].[Products]
GO

CREATE TABLE [dbo].[Products](
	[NodeID] [int] IDENTITY(1,1) NOT NULL,
	[ParentNodeID] [int] NULL,
	[Text] [nvarchar](50) NULL,
	[ShowItem] [bit] NULL,
	[IsEnabled] [bit] NULL,
	[CustID] [int] NULL,
	[Active] [nchar](10) NULL,
	[IsHidden] BIT,
 CONSTRAINT [PK_Products] 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




SET IDENTITY_INSERT [dbo].[Products] ON 

GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (1, NULL, N'Root Folder 1', NULL, NULL, 1, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (2, NULL, N'Root Folder  2', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (3, 1, N'Book 1', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (4, 2, N'Book 2', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (5, 2, N'Book 3', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (6, NULL, N'Root Folder  3', NULL, 1, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (7, 6, N'Book 4', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (8, 6, N'Book 5', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (9, 6, N'Sub Folder 1', NULL, 1, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (10, 9, N'Book 6', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (11, 9, N'Book 7', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (12, 9, N'Book 8', NULL, NULL, NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[Products] OFF
GO


; WITH NodePath AS (
	SELECT
		p1.[NodeID] AS [RootNodeID],
		p1.[NodeID] AS [NodeID],
		p1.[ParentNodeID],
		p1.[IsHidden]
	FROM
		Products AS p1
	-- Recurse
	UNION ALL SELECT
		np.[NodeID],
		p2.[NodeID],
		p2.[ParentNodeID],
		p2.[IsHidden]
	FROM
		Products AS p2
		JOIN NodePath AS np ON np.[NodeID] = p2.[ParentNodeID]
	)
SELECT *
FROM Products AS p
WHERE NOT EXISTS (
	SELECT 1
	FROM NodePath AS np
	WHERE np.[RootNodeID] = p.[NodeID]
	  AND np.[IsHidden] = 1
	  )

Note that I changed your table definition as there was no [IsHidden] Column

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2013-05-15 : 06:53:40

Visakh's solution uses a recursive pathing trick to make sure there are no circular reference.

If your hierarchy is a strict single parent then you don't need it. (as long as your data is consistent and there are no loops in the parent chain)

Visakh's also incorporates your ClientID condition -- I left it out as your example didn't produce any results. But yeah -- you should put it into the CTE as it vastly reduces the number of rows worked on.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-05-15 : 07:17:07

quote:

Originally posted by visakh16

Sounds like this to me


;With CTE
AS
(
SELECT     NodeId, ParentNodeId, Text, Active, CustID,ShowItem,CAST(NodeId AS Varchar(max)) AS [Path]
FROM         dbo.Products p
WHERE     (CustID = @CustID) AND (Active = 1)
AND ParentNodeID IS NULL

UNION ALL

SELECT     p.NodeId, p.ParentNodeId, p.Text, p.Active, p.CustID,p.ShowItem,CAST(c.[Path] + '/' + CAST(p.NodeId AS varchar(10)) AS Varchar(max)) 
FROM         dbo.Products p
INNER JOIN CTE c
ON c.NodeId = p.ParentNodeId
WHERE     (CustID = @CustID) AND (Active = 1)
)

SELECT *
FROM CTE c
WHERE ShowItem=1
AND NOT EXISTS (SELECT 1 
               FROM CTE
               WHERE '/' + Path + '/' LIKE '%/' + CAST(c.NodeID AS varchar(10)) + '/%'
               AND ShowItem = 0
               )
ORDER BY [Text]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

I cannot get this to work, the Query doesn't give any result at all....

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-05-15 : 07:18:53

quote:

Originally posted by Transact Charlie

Here's one way -- probably not the best way. Using recursion:



USE Tempdb
GO

/****** Object:  Table [dbo].[Products]    Script Date: 2013-05-15 10:50:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DROP TABLE [dbo].[Products]
GO

CREATE TABLE [dbo].[Products](
	[NodeID] [int] IDENTITY(1,1) NOT NULL,
	[ParentNodeID] [int] NULL,
	[Text] [nvarchar](50) NULL,
	[ShowItem] [bit] NULL,
	[IsEnabled] [bit] NULL,
	[CustID] [int] NULL,
	[Active] [nchar](10) NULL,
	[IsHidden] BIT,
 CONSTRAINT [PK_Products] 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




SET IDENTITY_INSERT [dbo].[Products] ON 

GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (1, NULL, N'Root Folder 1', NULL, NULL, 1, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (2, NULL, N'Root Folder  2', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (3, 1, N'Book 1', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (4, 2, N'Book 2', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (5, 2, N'Book 3', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (6, NULL, N'Root Folder  3', NULL, 1, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (7, 6, N'Book 4', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (8, 6, N'Book 5', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (9, 6, N'Sub Folder 1', NULL, 1, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (10, 9, N'Book 6', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (11, 9, N'Book 7', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (12, 9, N'Book 8', NULL, NULL, NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[Products] OFF
GO


; WITH NodePath AS (
	SELECT
		p1.[NodeID] AS [RootNodeID],
		p1.[NodeID] AS [NodeID],
		p1.[ParentNodeID],
		p1.[IsHidden]
	FROM
		Products AS p1
	-- Recurse
	UNION ALL SELECT
		np.[NodeID],
		p2.[NodeID],
		p2.[ParentNodeID],
		p2.[IsHidden]
	FROM
		Products AS p2
		JOIN NodePath AS np ON np.[NodeID] = p2.[ParentNodeID]
	)
SELECT *
FROM Products AS p
WHERE NOT EXISTS (
	SELECT 1
	FROM NodePath AS np
	WHERE np.[RootNodeID] = p.[NodeID]
	  AND np.[IsHidden] = 1
	  )

Note that I changed your table definition as there was no [IsHidden] Column

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

When I use this I get items that are placed in subfolder 3 that is hidden, and all items that are placed in that folder should be excluded.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2013-05-15 : 09:26:03

Ah yeah -- made an error in the recursive join. Try this


USE Tempdb
GO

/****** Object:  Table [dbo].[Products]    Script Date: 2013-05-15 10:50:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DROP TABLE [dbo].[Products]
GO

CREATE TABLE [dbo].[Products](
	[NodeID] [int] IDENTITY(1,1) NOT NULL,
	[ParentNodeID] [int] NULL,
	[Text] [nvarchar](50) NULL,
	[ShowItem] [bit] NULL,
	[IsEnabled] [bit] NULL,
	[CustID] [int] NULL,
	[Active] [nchar](10) NULL,
	[IsHidden] BIT,
 CONSTRAINT [PK_Products] 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




SET IDENTITY_INSERT [dbo].[Products] ON 

GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (1, NULL, N'Root Folder 1', NULL, NULL, 1, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (2, NULL, N'Root Folder  2', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (3, 1, N'Book 1', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (4, 2, N'Book 2', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (5, 2, N'Book 3', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (6, NULL, N'Root Folder  3', NULL, 1, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (7, 6, N'Book 4', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (8, 6, N'Book 5', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (9, 6, N'Sub Folder 1', NULL, 1, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (10, 9, N'Book 6', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (11, 9, N'Book 7', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (12, 9, N'Book 8', NULL, NULL, NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[Products] OFF
GO


; WITH NodePath AS (
	SELECT
		p1.[NodeID] AS [RootNodeID],
		p1.[NodeID] AS [NodeID],
		p1.[ParentNodeID],
		p1.[IsHidden]
	FROM
		Products AS p1
	-- Recurse
	UNION ALL SELECT
		np.[NodeID],
		p2.[NodeID],
		p2.[ParentNodeID],
		p2.[IsHidden]
	FROM
		Products AS p2
		JOIN NodePath AS np ON np.[ParentNodeID] = p2.[NodeID]
	)

SELECT *
FROM Products AS p
WHERE NOT EXISTS (
	SELECT 1
	FROM NodePath AS np
	WHERE np.[RootNodeID] = p.[NodeID]
	  AND np.[IsHidden] = 1
	  )

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-05-15 : 09:44:53

quote:

Originally posted by Transact Charlie

Ah yeah -- made an error in the recursive join. Try this


USE Tempdb
GO

/****** Object:  Table [dbo].[Products]    Script Date: 2013-05-15 10:50:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DROP TABLE [dbo].[Products]
GO

CREATE TABLE [dbo].[Products](
	[NodeID] [int] IDENTITY(1,1) NOT NULL,
	[ParentNodeID] [int] NULL,
	[Text] [nvarchar](50) NULL,
	[ShowItem] [bit] NULL,
	[IsEnabled] [bit] NULL,
	[CustID] [int] NULL,
	[Active] [nchar](10) NULL,
	[IsHidden] BIT,
 CONSTRAINT [PK_Products] 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




SET IDENTITY_INSERT [dbo].[Products] ON 

GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (1, NULL, N'Root Folder 1', NULL, NULL, 1, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (2, NULL, N'Root Folder  2', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (3, 1, N'Book 1', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (4, 2, N'Book 2', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (5, 2, N'Book 3', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (6, NULL, N'Root Folder  3', NULL, 1, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (7, 6, N'Book 4', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (8, 6, N'Book 5', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (9, 6, N'Sub Folder 1', NULL, 1, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (10, 9, N'Book 6', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (11, 9, N'Book 7', NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Products] ([NodeID], [ParentNodeID], [Text], [ShowItem], [IsHidden], [CustID], [Active]) VALUES (12, 9, N'Book 8', NULL, NULL, NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[Products] OFF
GO


; WITH NodePath AS (
	SELECT
		p1.[NodeID] AS [RootNodeID],
		p1.[NodeID] AS [NodeID],
		p1.[ParentNodeID],
		p1.[IsHidden]
	FROM
		Products AS p1
	-- Recurse
	UNION ALL SELECT
		np.[NodeID],
		p2.[NodeID],
		p2.[ParentNodeID],
		p2.[IsHidden]
	FROM
		Products AS p2
		JOIN NodePath AS np ON np.[ParentNodeID] = p2.[NodeID]
	)

SELECT *
FROM Products AS p
WHERE NOT EXISTS (
	SELECT 1
	FROM NodePath AS np
	WHERE np.[RootNodeID] = p.[NodeID]
	  AND np.[IsHidden] = 1
	  )

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Thanks, that works much better, I tried to only select the ones that have CustID=1 but I can't get that to work, would you mind showing me how?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2013-05-15 : 09:56:45

I take it you mean that have CustID = 1 anywhere in the parent hierarchy?

This will probably work.....



DECLARE @custID INT = 1

; WITH NodePath AS (
	SELECT
		p1.[NodeID] AS [RootNodeID],
		p1.[NodeID] AS [NodeID],
		p1.[ParentNodeID],
		p1.[IsHidden],
		p1.[CustID]
	FROM
		Products AS p1
	-- Recurse
	UNION ALL SELECT
		np.[NodeID],
		p2.[NodeID],
		p2.[ParentNodeID],
		p2.[IsHidden],
		p2.[CustID]
	FROM
		Products AS p2
		JOIN NodePath AS np ON np.[ParentNodeID] = p2.[NodeID]
	)

SELECT *
FROM Products AS p
WHERE NOT EXISTS (
	SELECT 1
	FROM NodePath AS np
	WHERE np.[RootNodeID] = p.[NodeID]
	  AND np.[IsHidden] = 1
	  )
	AND EXISTS (
	SELECT 1
	FROM NodePath AS np
	WHERE np.[RootNodeID] = p.[NodeID]
	  AND np.[CustID] = @CustID
	  )

Results:


NodeID      ParentNodeID Text                                               ShowItem IsEnabled CustID      Active     IsHidden
----------- ------------ -------------------------------------------------- -------- --------- ----------- ---------- --------
1           NULL         Root Folder 1                                      NULL     NULL      1           NULL       NULL
3           1            Book 1                                             NULL     NULL      NULL        NULL       NULL

Note that if you have a lot of data -- then building that mapping isn't very efficient. But because of the way that the data is modeled I'm having a hard time thinking of an efficient way to pre filter it.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-05-15 : 10:03:32

Excellent! Thank you very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources