SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Hide node and its sub nodes
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

magmo
Aged Yak Warrior

512 Posts

Posted - 05/15/2013 :  06:26:34  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 05/15/2013 :  06:40:19  Show Profile  Reply with Quote
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

Edited by - visakh16 on 05/15/2013 06:41:09
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 05/15/2013 :  06:51:13  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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/
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 05/15/2013 :  06:53:40  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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/

Edited by - Transact Charlie on 05/15/2013 06:55:50
Go to Top of Page

magmo
Aged Yak Warrior

512 Posts

Posted - 05/15/2013 :  07:17:07  Show Profile  Reply with Quote
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....
Go to Top of Page

magmo
Aged Yak Warrior

512 Posts

Posted - 05/15/2013 :  07:18:53  Show Profile  Reply with Quote
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.
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 05/15/2013 :  09:26:03  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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/
Go to Top of Page

magmo
Aged Yak Warrior

512 Posts

Posted - 05/15/2013 :  09:44:53  Show Profile  Reply with Quote
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?
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 05/15/2013 :  09:56:45  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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/
Go to Top of Page

magmo
Aged Yak Warrior

512 Posts

Posted - 05/15/2013 :  10:03:32  Show Profile  Reply with Quote
Excellent! Thank you very much!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000