hits counter
Google
Pinterest
Linkedin
YouTube
Skype

Find all parents and children
using SQL query and Common Table Expression

Quick Inquiry

Client Speaks

Client Speaks - Testimonials for Aegis Software


You Are @ >> Home >> Articles >>Find all parents and children using SQL query and Common Table Expression

Today I am going to explain how to get all parents and children of a particular record using SQL query and Common Table Expression. At 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 particular level but you can use it to find both till N level. For the users, who wants 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 few records to demonstrate how the solution will work.

Following is the script to create two tables with test data.

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.

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

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 more clean and easy to understand, I distributed my query into 3 parts. For those who requires to get only

Query Part 1 : Find only Parents of a particular record

Following query is use to get record and its all parent category records from above tables.

;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 result as following. 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 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
Following query is use to get record and its all child category records from above tables.

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

;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 work like a magic to achieve the result which look quite complex at first stage. The Common Table Extension (CTE) and recursion call in SQL query make it very easy and works very effectively till N level to find related records for you.

Aegis software development team has created this technical document for helping all .net developers worldwide.


For further information, mail us at info@aegissoftwares.com