Answered by:
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.
Monday, May 20, 2013 10:42 AM
Answers
-
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 )
- Marked as answer by Jon Bellamy Monday, May 20, 2013 2:10 PM
Monday, May 20, 2013 1:52 PM
All replies
-
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 )
Monday, May 20, 2013 11:13 AM -
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.- Edited by Jon Bellamy Monday, May 20, 2013 11:49 AM Missed Question
Monday, May 20, 2013 11:42 AM -
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.
Monday, May 20, 2013 11:53 AM -
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.
Monday, May 20, 2013 12:26 PM -
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 )
- Edited by Jesús López Monday, May 20, 2013 1:25 PM
Monday, May 20, 2013 1:24 PM -
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?
Monday, May 20, 2013 1:25 PM -
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?
Monday, May 20, 2013 1:29 PM -
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.
Monday, May 20, 2013 1:34 PM -
Hi Jesús,
Unfortunately, your latest script returns 1007 and 1008 as part of the results.
I'm expecting it do the following:
- Using Item Id 17
- Select Item's Container (Container ID 4 in this case)
- Select Step 2's Base Container (Container ID 2 in this case)
- Select Items children within Step 3 with relationship to Step 1 ONLY (Item ID's 13,14,15 in this case)
- Select current Items' Container (Container ID 2 in this case)
- Select step 5's Base Container (Container ID 1 in this case)
- Select Items' children within Step 6 base container with relationship to items in step 4 ONLY (Item ID's 1 - 9 in this case)
- Return 7
Monday, May 20, 2013 1:42 PM -
Hi Latheesh,
The latest version merely returns all containers in the database (for me).
I need the Items, limited to within the container hierarchy.
Monday, May 20, 2013 1:43 PM -
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 )
- Marked as answer by Jon Bellamy Monday, May 20, 2013 2:10 PM
Monday, May 20, 2013 1:52 PM -
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
Monday, May 20, 2013 2:10 PM -
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 )
Monday, May 20, 2013 2:17 PM -
Thank you again Jesús.Monday, May 20, 2013 2:25 PM