locked
Common Table Expression Help RRS feed

  • 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
    	)


    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:

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