Common Table Expression Help

Question

Hi, I'm currently running a query using Linq-to-SQL and SelectMany but suspect that not only is it expensive, it is not as efficient as it should be.

I have the following segment of my database:

USE [TestDatabase]
GO
/****** Object:  Table [dbo].[Containers]    Script Date: 16/05/2013 20:42:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Containers](
    [ContainerId] [int] IDENTITY(1,1) NOT NULL,
    [BaseContainerId] [int] NULL,
    [Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Containers] PRIMARY KEY CLUSTERED 
(
    [ContainerId] 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
/****** Object:  Table [dbo].[ItemRelationships]    Script Date: 16/05/2013 20:42:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ItemRelationships](
    [ChildItemId] [int] NOT NULL,
    [ParentItemId] [int] NOT NULL,
 CONSTRAINT [PK_ItemRelationships] PRIMARY KEY CLUSTERED 
(
    [ChildItemId] ASC,
    [ParentItemId] 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
/****** Object:  Table [dbo].[Items]    Script Date: 16/05/2013 20:42:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Items](
    [ItemId] [int] IDENTITY(1,1) NOT NULL,
    [ContainerId] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED 
(
    [ItemId] 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].[Containers] ON 

INSERT [dbo].[Containers] ([ContainerId], [BaseContainerId], [Name]) VALUES (1, NULL, N'Level 1')
INSERT [dbo].[Containers] ([ContainerId], [BaseContainerId], [Name]) VALUES (2, 1, N'Level 2')
INSERT [dbo].[Containers] ([ContainerId], [BaseContainerId], [Name]) VALUES (3, 1, N'Level 2b')
INSERT [dbo].[Containers] ([ContainerId], [BaseContainerId], [Name]) VALUES (4, 2, N'Level 3')
SET IDENTITY_INSERT [dbo].[Containers] OFF
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (1, 13)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (2, 13)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (3, 13)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (4, 14)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (5, 14)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (6, 14)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (7, 15)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (8, 15)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (9, 15)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (10, 16)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (11, 16)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (12, 16)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (13, 17)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (14, 17)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (15, 17)
SET IDENTITY_INSERT [dbo].[Items] ON 

INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (1, 1, N'A')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (2, 1, N'B')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (3, 1, N'C')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (4, 1, N'D')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (5, 1, N'E')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (6, 1, N'F')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (7, 1, N'G')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (8, 1, N'H')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (9, 1, N'I')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (10, 1, N'J')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (11, 1, N'K')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (12, 1, N'L')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (13, 2, N'A2')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (14, 2, N'A2')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (15, 2, N'C2')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (16, 3, N'D2B')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (17, 4, N'A3')
SET IDENTITY_INSERT [dbo].[Items] OFF
ALTER TABLE [dbo].[Containers]  WITH CHECK ADD  CONSTRAINT [FK_Containers_Containers] FOREIGN KEY([BaseContainerId])
REFERENCES [dbo].[Containers] ([ContainerId])
GO
ALTER TABLE [dbo].[Containers] CHECK CONSTRAINT [FK_Containers_Containers]
GO
ALTER TABLE [dbo].[ItemRelationships]  WITH CHECK ADD  CONSTRAINT [FK_ItemRelationships_ChildItems] FOREIGN KEY([ParentItemId])
REFERENCES [dbo].[Items] ([ItemId])
GO
ALTER TABLE [dbo].[ItemRelationships] CHECK CONSTRAINT [FK_ItemRelationships_ChildItems]
GO
ALTER TABLE [dbo].[ItemRelationships]  WITH CHECK ADD  CONSTRAINT [FK_ItemRelationships_ParentItems] FOREIGN KEY([ChildItemId])
REFERENCES [dbo].[Items] ([ItemId])
GO
ALTER TABLE [dbo].[ItemRelationships] CHECK CONSTRAINT [FK_ItemRelationships_ParentItems]
GO
ALTER TABLE [dbo].[Items]  WITH CHECK ADD  CONSTRAINT [FK_Items_Containers] FOREIGN KEY([ContainerId])
REFERENCES [dbo].[Containers] ([ContainerId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Items] CHECK CONSTRAINT [FK_Items_Containers]
GO

I've written the following query (I am a complete novice to direct SQL queries)

DECLARE @itemId BIGINT = 17;

SELECT
    Items.ItemId
FROM
    [TestDatabase].[dbo].[ItemRelationships]
INNER JOIN
    [TestDatabase].[dbo].Items
ON
    ItemRelationships.ChildItemId = Items.ItemId
INNER JOIN
    [TestDatabase].[dbo].[Containers]
ON
    Items.ContainerId = Containers.ContainerId
WHERE
    ItemRelationships.ParentItemId = @itemId
AND
    Items.ContainerId = 
    (
    SELECT
        BaseContainerId
    FROM
        [TestDatabase].[dbo].[Items]
    INNER JOIN
        [TestDatabase].[dbo].[Containers]
    ON
        Items.ContainerId = Containers.ContainerId
    WHERE
        Items.ItemId = @itemId
    )

Which correctly returns Item Ids 13, 14 and 15.  What I need is the query (and I suspect via a CTE) to run past Items 13, 14 and 15 to get their child items (Id's 1 -9 inclusive).  However the query (as you'll see from the sub-query within the above) MUST check the child items belong to the base container of the container before it - if that make sense?

Can anyone help?  I've pulled my hair out trying to make a CTE work but I just cannot get my head around them.

Expected results would be:

• For ItemId 1, return Id 1 (Item 1 is in container 1 with base of NULL)
• For ItemId 13, return Ids 1, 2 and 3 (Item is in container 2 which has a base container of 1, which in turn has a base container of NULL)
• For ItemId 17, return Ids 1 - 9 through their link to Ids 13, 14 and 15 (items are in container 4 which has a base container of 2 ... 1 ... NULL)

Thank you in advance.

Answer 1

I don't understand the logic of your query. What are the role of containers? Do you want just the leaf nodes?

This query retunrs what you said, but ignores containers:

DECLARE @ItemId int
SET @ItemId = 17;
WITH H
AS
(
	SELECT I.ItemId
	FROM dbo.Items I
	WHERE I.ItemId = @ItemId
	
	UNION ALL
	
	SELECT IR.ChildItemId
	FROM 
		dbo.ItemRelationships IR
		INNER JOIN H ON H.ItemId = IR.ParentItemId

)
SELECT H.ItemId
FROM H
WHERE 
	NOT EXISTS(
		SELECT *
		FROM dbo.ItemRelationships IR
		WHERE IR.ParentItemId = H.ItemId
	)
	

Answer 2

Hi Jesús,

Firstly, thank you for your response (and the partial answer) - it's still way close than I managed to get!

Containers hold a set type of item.  For example Type A or Type B.  All Items within a container are therefore that Type.  In this particular scenario, the all items are of type A, but Items of Type B can be associated to Type A items through ItemRelationships, however they must not be selected when getting all base items of Type A.

Additionally, it is worth me mentioning that Containers of Type B cannot ever be a base container of Type A.

Does that make sense?

EDIT - Also, by leaf nodes (if you mean just listing the end Items - sorry my understanding of SQL terms is limited) then yes, as your reply already does.

Answer 3

I'm afraid I'm not following you. Sorry.

Could you post an expected result that show us how containers matter? An expected result that shows how containers filters out items would be usefull.

Answer 4

Try the below:

DECLARE @itemId BIGINT = 15;
;With cte
AS
(
	Select A.*,B.ChildItemId,A.ContainerId From Items A
	Inner Join ItemRelationships B on A.ItemId = B.ParentItemId Where A.ItemId = @itemId
	
	Union All
	
	Select B.*,A.ChildItemId,B.ContainerId  From cte A
	Inner Join Items B On B.ItemId = A.ChildItemId and A.ContainerId <>B.ContainerId
)
--Select * From cte
,cte1
As
(
	Select A.ContainerId,B.BaseContainerId From cte A
	Inner Join Containers B on A.ContainerId = B.ContainerId
	
	Union All
	
	Select B.BaseContainerId,B.ContainerId   From cte1 A
	Inner Join Containers B On B.ContainerId = A.BaseContainerId and B.BaseContainerId <>1
)
Select distinct containerID From (Select ContainerId containerID From cte1
Union All
Select 1) A

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 5

Well, despite I'm not sure what you actually want, I will do a try:

The following query does the same as the previous one but shows only those items that are in a container that is a descendant of the root container of Item @ItemId or the root container itself.

DECLARE @ItemId int
SET @ItemId = 17;
WITH H
AS
(
	SELECT I.ItemId, I.ContainerId
	FROM dbo.Items I
	WHERE I.ItemId = @ItemId
	
	UNION ALL
	
	SELECT I.ItemId, I.ContainerId
	FROM 
		dbo.ItemRelationships IR
		INNER JOIN H ON H.ItemId = IR.ParentItemId
		INNER JOIN dbo.Items I ON IR.ChildItemId = I.ItemId

), BC -- recursive base container
AS
(
	SELECT C.ContainerId, C.BaseContainerId
	FROM 
		dbo.Items I 
		INNER JOIN dbo.Containers C
			ON I.ContainerId = C.ContainerId
			
	UNION ALL
	
	SELECT C.ContainerId, C.BaseContainerId
	FROM
		dbo.Containers C
		INNER JOIN BC ON BC.BaseContainerId = C.BaseContainerId
			
), C -- Containers
AS
(
	-- The anchor is the root container
	SELECT 
		BC.ContainerId
	FROM 
		BC
	WHERE
		BC.BaseContainerId IS NULL -- 
		
	UNION ALL
	
	-- Child containers
	SELECT CNT.ContainerId
	FROM
		dbo.Containers CNT
		INNER JOIN C ON C.ContainerId = CNT.BaseContainerId

)
SELECT H.ItemId, H.ContainerId
FROM H
WHERE 
	NOT EXISTS(
		SELECT *
		FROM dbo.ItemRelationships IR
		WHERE IR.ParentItemId = H.ItemId
	)
	AND H.ContainerId IN (
		SELECT c.ContainerId
		FROM C
	)

Answer 6

Hi Jesús,

That's not your fault, its more my ability to simplify my description.

Try the following to create the Test DB with new rows:

USE [TestDatabase]
GO
/****** Object:  Table [dbo].[Containers]    Script Date: 20/05/2013 14:17:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Containers](
	[ContainerId] [int] IDENTITY(1,1) NOT NULL,
	[BaseContainerId] [int] NULL,
	[Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Containers] PRIMARY KEY CLUSTERED 
(
	[ContainerId] 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
/****** Object:  Table [dbo].[ItemRelationships]    Script Date: 20/05/2013 14:17:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ItemRelationships](
	[ChildItemId] [int] NOT NULL,
	[ParentItemId] [int] NOT NULL,
 CONSTRAINT [PK_ItemRelationships] PRIMARY KEY CLUSTERED 
(
	[ChildItemId] ASC,
	[ParentItemId] 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
/****** Object:  Table [dbo].[Items]    Script Date: 20/05/2013 14:17:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Items](
	[ItemId] [int] IDENTITY(1,1) NOT NULL,
	[ContainerId] [int] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED 
(
	[ItemId] 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].[Containers] ON 

INSERT [dbo].[Containers] ([ContainerId], [BaseContainerId], [Name]) VALUES (1, NULL, N'Level 1')
INSERT [dbo].[Containers] ([ContainerId], [BaseContainerId], [Name]) VALUES (2, 1, N'Level 2')
INSERT [dbo].[Containers] ([ContainerId], [BaseContainerId], [Name]) VALUES (3, 1, N'Level 2b')
INSERT [dbo].[Containers] ([ContainerId], [BaseContainerId], [Name]) VALUES (4, 2, N'Level 3')
INSERT [dbo].[Containers] ([ContainerId], [BaseContainerId], [Name]) VALUES (5, NULL, N'TypeB')
SET IDENTITY_INSERT [dbo].[Containers] OFF
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (1, 13)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (2, 13)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (3, 13)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (4, 14)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (5, 14)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (6, 14)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (7, 15)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (8, 15)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (9, 15)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (10, 16)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (11, 16)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (12, 16)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (13, 17)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (14, 17)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (15, 17)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (1007, 13)
INSERT [dbo].[ItemRelationships] ([ChildItemId], [ParentItemId]) VALUES (1008, 17)
SET IDENTITY_INSERT [dbo].[Items] ON 

INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (1, 1, N'A')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (2, 1, N'B')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (3, 1, N'C')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (4, 1, N'D')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (5, 1, N'E')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (6, 1, N'F')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (7, 1, N'G')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (8, 1, N'H')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (9, 1, N'I')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (10, 1, N'J')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (11, 1, N'K')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (12, 1, N'L')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (13, 2, N'A2')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (14, 2, N'A2')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (15, 2, N'C2')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (16, 3, N'D2B')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (17, 4, N'A3')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (1007, 5, N'TypeB1')
INSERT [dbo].[Items] ([ItemId], [ContainerId], [Name]) VALUES (1008, 5, N'TypeB2')
SET IDENTITY_INSERT [dbo].[Items] OFF
ALTER TABLE [dbo].[Containers]  WITH CHECK ADD  CONSTRAINT [FK_Containers_Containers] FOREIGN KEY([BaseContainerId])
REFERENCES [dbo].[Containers] ([ContainerId])
GO
ALTER TABLE [dbo].[Containers] CHECK CONSTRAINT [FK_Containers_Containers]
GO
ALTER TABLE [dbo].[ItemRelationships]  WITH CHECK ADD  CONSTRAINT [FK_ItemRelationships_ChildItems] FOREIGN KEY([ParentItemId])
REFERENCES [dbo].[Items] ([ItemId])
GO
ALTER TABLE [dbo].[ItemRelationships] CHECK CONSTRAINT [FK_ItemRelationships_ChildItems]
GO
ALTER TABLE [dbo].[ItemRelationships]  WITH CHECK ADD  CONSTRAINT [FK_ItemRelationships_ParentItems] FOREIGN KEY([ChildItemId])
REFERENCES [dbo].[Items] ([ItemId])
GO
ALTER TABLE [dbo].[ItemRelationships] CHECK CONSTRAINT [FK_ItemRelationships_ParentItems]
GO
ALTER TABLE [dbo].[Items]  WITH CHECK ADD  CONSTRAINT [FK_Items_Containers] FOREIGN KEY([ContainerId])
REFERENCES [dbo].[Containers] ([ContainerId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Items] CHECK CONSTRAINT [FK_Items_Containers]
GO
USE [master]
GO
ALTER DATABASE [TestDatabase] SET  READ_WRITE 
GO

Here you'll see a new Container (ID 5) with no Base Container.  It contains two items (ID 1007 which is linked to ID 13) and (ID 1008 which is linked to ID 17).  When retrieving results for IDs 13 and 17, the results should ignore 1007 and 1008 because they belong to a container that is not within the hierarchy of ID 17's container.

In essence, your previous results are spot on (1 - 9), but 1007 and 1008 are now included when using your first response.

Does that make more sense?

Answer 7

Hi Latheesh,

Thank you for your input.

Unless I am doing something wrong, this selects all of the base containers only (not including the initial item's container.

That said, I have noticed the query window saying that "The column `ContainerId` was specified multiple times for `cte`".  I'm probably missing something simple to correct?

Answer 8

That works for me.

Try the below:

DECLARE @itemId BIGINT = 17;
;With cte
AS
(
	Select B.ChildItemId,A.ContainerId From Items A
	Inner Join ItemRelationships B on A.ItemId = B.ParentItemId Where A.ItemId = @itemId
	
	Union All
	
	Select A.ChildItemId,B.ContainerId   From cte A
	Inner Join Items B On B.ItemId = A.ChildItemId and A.ContainerId <>B.ContainerId
)
--Select * From cte
,cte1
As
(
	Select A.ContainerId,B.BaseContainerId From cte A
	Inner Join Containers B on A.ContainerId = B.ContainerId
	
	Union All
	
	Select B.BaseContainerId,B.ContainerId   From cte1 A
	Inner Join Containers B On B.ContainerId = A.BaseContainerId and B.BaseContainerId <>1
)
Select distinct containerID From (Select ContainerId containerID From cte1
Union All
Select 1) A

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 9

Hi Jesús,

Unfortunately, your latest script returns 1007 and 1008 as part of the results.

I'm expecting it do the following:

1. Using Item Id 17
2. Select Item's Container (Container ID 4 in this case)
3. Select Step 2's Base Container (Container ID 2 in this case)
4. Select Items children within Step 3 with relationship to Step 1 ONLY (Item ID's 13,14,15 in this case)
5. Select current Items' Container (Container ID 2 in this case)
6. Select step 5's Base Container (Container ID 1 in this case)
7. Select Items' children within Step 6 base container with relationship to items in step 4 ONLY (Item ID's 1 - 9 in this case)
8. Return 7

Answer 10

Hi Latheesh,

The latest version merely returns all containers in the database (for me).

I need the Items, limited to within the container hierarchy.

Answer 11

The previous query had some typos, sorry.

I think this one will work for you:

DECLARE @ItemId int
SET @ItemId = 17;
WITH H
AS
(
	SELECT I.ItemId, I.ContainerId
	FROM dbo.Items I
	WHERE I.ItemId = @ItemId
	
	UNION ALL
	
	SELECT I.ItemId, I.ContainerId
	FROM 
		dbo.ItemRelationships IR
		INNER JOIN H ON H.ItemId = IR.ParentItemId
		INNER JOIN dbo.Items I ON IR.ChildItemId = I.ItemId

), BC -- recursive base container
AS
(
	SELECT C.ContainerId, C.BaseContainerId
	FROM 
		dbo.Items I 
		INNER JOIN dbo.Containers C
			ON I.ContainerId = C.ContainerId
	WHERE
		I.ItemId = @ItemId
			
	UNION ALL
	
	SELECT C.ContainerId, C.BaseContainerId
	FROM
		dbo.Containers C
		INNER JOIN BC ON BC.BaseContainerId = C.ContainerId
			
)
, C -- Containers
AS
(
	-- The anchor is the root container
	SELECT 
		BC.ContainerId
	FROM 
		BC
	WHERE
		BC.BaseContainerId IS NULL -- 
		
	UNION ALL
	
	-- Child containers
	SELECT CNT.ContainerId
	FROM
		dbo.Containers CNT
		INNER JOIN C ON C.ContainerId = CNT.BaseContainerId

)
SELECT H.ItemId, H.ContainerId
FROM H
WHERE 
	NOT EXISTS(
		SELECT *
		FROM dbo.ItemRelationships IR
		WHERE IR.ParentItemId = H.ItemId
	)
	AND H.ContainerId IN (
		SELECT c.ContainerId
		FROM C
	)
	

Answer 12

Hi Jesús,

Very, very cool.  And works precisely as expected.

Could I be cheeky and just ask how to modify it if I wanted to simply return all of the Item's as full rows as opposed to just the Id's?

That said, thank you so much for bearing with me.  I've pulled my hair out for a few days trying to solve this.  I really do appreciate your efforts and am very, very grateful.

Best wishes, Jon

Answer 13

I'm glad to hear that.

If you want more columns just add them:

DECLARE @ItemId int
SET @ItemId = 17;
WITH H
AS
(
	SELECT I.ItemId, I.ContainerId, I.Name
	FROM dbo.Items I
	WHERE I.ItemId = @ItemId
	
	UNION ALL
	
	SELECT I.ItemId, I.ContainerId, I.Name
	FROM 
		dbo.ItemRelationships IR
		INNER JOIN H ON H.ItemId = IR.ParentItemId
		INNER JOIN dbo.Items I ON IR.ChildItemId = I.ItemId

), BC -- recursive base container
AS
(
	SELECT C.ContainerId, C.BaseContainerId
	FROM 
		dbo.Items I 
		INNER JOIN dbo.Containers C
			ON I.ContainerId = C.ContainerId
	WHERE
		I.ItemId = @ItemId
			
	UNION ALL
	
	SELECT C.ContainerId, C.BaseContainerId
	FROM
		dbo.Containers C
		INNER JOIN BC ON BC.BaseContainerId = C.ContainerId
			
)
, C -- Containers
AS
(
	-- The anchor is the root container
	SELECT 
		BC.ContainerId
	FROM 
		BC
	WHERE
		BC.BaseContainerId IS NULL -- 
		
	UNION ALL
	
	-- Child containers
	SELECT CNT.ContainerId
	FROM
		dbo.Containers CNT
		INNER JOIN C ON C.ContainerId = CNT.BaseContainerId

)
SELECT H.ItemId, H.ContainerId, H.Name
FROM H
WHERE 
	NOT EXISTS(
		SELECT *
		FROM dbo.ItemRelationships IR
		WHERE IR.ParentItemId = H.ItemId
	)
	AND H.ContainerId IN (
		SELECT c.ContainerId
		FROM C
	)
	

Answer 14

Thank you again Jesús.