Today I am going to explain how to get all parents and children of a particular record using SQL query and Common Table Expression. In the beginning, this seems to be much complex to develop but the use of Common Table Extension (CTE) and recursion call in SQL makes it too easier for you. It will not only find Parent of children for a particular level but you can use it to find both till N level. For the users, who want to work with hierarchical data relationships, this will work like a dream. For my example, I will create two tables to manage Master and relationship entries. The two tables will be Categories and CategoryRelation. I also added a few records to demonstrate how the solution will work. Following is the script to create two tables with test data.
<![CDATA[
USE [Test] /*Test is database name*/
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Categories](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED ([CategoryID] 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 ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CategoryRelation](
[ParentCategoryID] [int] NOT NULL,
[ChildCategoryID] [int] NOT NULL
) ON [PRIMARY]
GO
]]>
After creating above two tables, I am adding some records for our testing.
<![CDATA[
SET IDENTITY_INSERT [dbo].[Categories] ON
INSERT [dbo].[Categories] ([CategoryID], [Name]) VALUES (1, N'Clothing')
INSERT [dbo].[Categories] ([CategoryID], [Name]) VALUES (2, N'Electronics')
INSERT [dbo].[Categories] ([CategoryID], [Name]) VALUES (3, N'Men''s Clothing')
INSERT [dbo].[Categories] ([CategoryID], [Name]) VALUES (4, N'Women''s Clothing')
INSERT [dbo].[Categories] ([CategoryID], [Name]) VALUES (5, N'Kid''s Clothing')
INSERT [dbo].[Categories] ([CategoryID], [Name]) VALUES (6, N'Jeans Pent')
INSERT [dbo].[Categories] ([CategoryID], [Name]) VALUES (7, N'T-Shirts')
INSERT [dbo].[Categories] ([CategoryID], [Name]) VALUES (8, N'Sarees')
INSERT [dbo].[Categories] ([CategoryID], [Name]) VALUES (9, N'Shirts')
INSERT [dbo].[Categories] ([CategoryID], [Name]) VALUES (10, N'Mobiles')
INSERT [dbo].[Categories] ([CategoryID], [Name]) VALUES (11, N'TVs')
INSERT [dbo].[Categories] ([CategoryID], [Name]) VALUES (12, N'Smartphone')
INSERT [dbo].[Categories] ([CategoryID], [Name]) VALUES (13, N'LED')
INSERT [dbo].[Categories] ([CategoryID], [Name]) VALUES (14, N'LCD')
INSERT [dbo].[Categories] ([CategoryID], [Name]) VALUES (15, N'Normal Mobile')
SET IDENTITY_INSERT [dbo].[Categories] OFF
]]>
<![CDATA[
INSERT [dbo].[CategoryRelation] ([ParentCategoryID], [ChildCategoryID]) VALUES (1, 3)
INSERT [dbo].[CategoryRelation] ([ParentCategoryID], [ChildCategoryID]) VALUES (1, 4)
INSERT [dbo].[CategoryRelation] ([ParentCategoryID], [ChildCategoryID]) VALUES (1, 5)
INSERT [dbo].[CategoryRelation] ([ParentCategoryID], [ChildCategoryID]) VALUES (3, 6)
INSERT [dbo].[CategoryRelation] ([ParentCategoryID], [ChildCategoryID]) VALUES (4, 6)
INSERT [dbo].[CategoryRelation] ([ParentCategoryID], [ChildCategoryID]) VALUES (5, 6)
INSERT [dbo].[CategoryRelation] ([ParentCategoryID], [ChildCategoryID]) VALUES (3, 7)
INSERT [dbo].[CategoryRelation] ([ParentCategoryID], [ChildCategoryID]) VALUES (3, 9)
INSERT [dbo].[CategoryRelation] ([ParentCategoryID], [ChildCategoryID]) VALUES (5, 7)
INSERT [dbo].[CategoryRelation] ([ParentCategoryID], [ChildCategoryID]) VALUES (5, 9)
INSERT [dbo].[CategoryRelation] ([ParentCategoryID], [ChildCategoryID]) VALUES (4, 8)
INSERT [dbo].[CategoryRelation] ([ParentCategoryID], [ChildCategoryID]) VALUES (2, 10)
INSERT [dbo].[CategoryRelation] ([ParentCategoryID], [ChildCategoryID]) VALUES (2, 11)
INSERT [dbo].[CategoryRelation] ([ParentCategoryID], [ChildCategoryID]) VALUES (10, 12)
INSERT [dbo].[CategoryRelation] ([ParentCategoryID], [ChildCategoryID]) VALUES (10, 15)
INSERT [dbo].[CategoryRelation] ([ParentCategoryID], [ChildCategoryID]) VALUES (11, 13)
INSERT [dbo].[CategoryRelation] ([ParentCategoryID], [ChildCategoryID]) VALUES (11, 14)
]]>
After inserting above records the data hierarchy will be something like this in database,
<![CDATA[
Clothing >> Men's Clothing >> Jeans Pent, T-Shirts and Shirts.
Clothing >> Kid's Clothing >> Jeans Pent, T-Shirts and Shirts.
Clothing >> Women's Clothing >> Jeans Pent and Sarees.
Another one is,
Electronics >> Mobiles >> Smartphones
Electronics >> Mobiles >> Normal mobile
Electronics >> TVs >> LED
Electronics >> TVs >> LCD
]]>
For making my explanation cleaner and easy to understand, I distributed my query into 3 parts. For those who require to get only
Query Part 1: Find only Parents of a particular record The following query is used to get the record and it’s all parent category records from the above tables.
<![CDATA[
;WITH cte AS (
SELECT a.*
FROM Categories a
WHERE a.Name = 'Mobiles'
UNION ALL
SELECT a.*
FROM Categories a
join CategoryRelation s
on a.CategoryID = s.ParentCategoryID
JOIN cte c ON s.ChildCategoryID = c.CategoryID
)
select * FROM cte
]]>
This query will produce a result as follows. If you don't want "Mobiles" in your result and only want parents then simply change the condition before "UNION ALL" to not return that (or any) record.
Here “Mobile” is a child category record with CategoryID 10 and “Electronics” is parent category record with CategoryId 2.
Query Part 2: Find only Children of a particular record the following query is used to get the record and its all child category records from the above tables.
<![CDATA[
;with Cte2 As (
SELECT a.*
FROM Categories a
WHERE a.Name = 'Mobiles'
UNION ALL
SELECT a.*
FROM Categories a
join CategoryRelation s
on a.CategoryID = s.ChildCategoryID
JOIN cte2 c ON s.ParentCategoryID = c.CategoryID
)
select * FROM Cte2
]]>
This query will produce a result as above. If you don't want "Mobiles" in your result and only want parents then simply change the condition before "UNION ALL" to not return that (or any) record. Here “Mobile” is Parent category record with CategoryID 10 and “Smartphones” and “Normal Mobile” is child category records with CategoryId 12 and 15.
Query Part 3: Find Parent and Children both of a particular record now I join above both two queries into 1 to get all parents and children records from my database tables. It will have also show how we can use multiple Common Table Extensions in single SQL Query.
<![CDATA[
;WITH cte AS (
SELECT a.*
FROM Categories a
WHERE a.Name = 'Mobiles'
UNION ALL
SELECT a.*
FROM Categories a
join CategoryRelation s
on a.CategoryID = s.ParentCategoryID
JOIN cte c ON s.ChildCategoryID = c.CategoryID
)
,Cte2 As (
SELECT a.*
FROM Categories a
WHERE a.Name = 'Mobiles'
UNION ALL
SELECT a.*
FROM Categories a
join CategoryRelation s
on a.CategoryID = s.ChildCategoryID
JOIN cte2 c ON s.ParentCategoryID = c.CategoryID
)
Select * from cte
Union SELECT * FROM cte2
]]>
This query will produce the result with all parents and children records as follow.
That's it!! This thing is works like magic to achieve the result which looks quite complex at first stage. The Common Table Extension (CTE) and recursion call in SQL query make it very easy and work very effectively till N level to find related records for you.
Aegis Software Microsoft Dynamics 365 Custom Development services team has created this technical document for helping all developers worldwide.
For further information, mail us at info@aegissoftwares.com
Read More:
