Dynamic query for Parent/Child

Often new developers gets tasks to create dynamic menus from database and the first thing they stuck is how to query the data from database.  I am not writing anything new; its just I want to write assuming it will help somebody someday.

To begin with I will create a new SQL Table first. The below table consists of four columns:

  1. MenuId
  2. MenuName
  3. Description
  4. ParentId

GO

/****** Object: Table [dbo].[tblMenu] Script Date: 03/09/2016 11:18:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblMenu](
[MenuID] [int] IDENTITY(1,1) NOT NULL,
[MenuName] [varchar](50) NULL,
[Description] [varchar](255) NULL,
[ParentID] [int] NULL,
CONSTRAINT [PK_Menu] PRIMARY KEY CLUSTERED
(
[MenuID] 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_PADDING OFF
GO

I inserted somesample data using below query.

INSERT INTO [tblMenu]
Select ‘Product’,’A List of Products’, NULL
UNION ALL Select ‘Applications’,’Appliations’,NULL
UNION ALL Select ‘Document’,’Documentation’, NULL
UNION ALL Select ‘Support’,’Support’, NULL
UNION ALL Select ‘Download’,’Download’, NULL
UNION ALL Select ‘Background’,’ProductBackground’, 1
UNION ALL Select ‘Details’,’Product Details’, 1
UNION ALL Select ‘Mobile Device’,’Mobile DeviceApplications’, 2
UNION ALL Select ‘Portal’,’Portal Applications’,2
UNION ALL Select ‘Web Applicaitons’,’WebApplications’, 2
UNION ALL Select ‘Demo’,’Demo Applicaitons’, 2
UNION ALL Select ‘Performance Tests’,’ApplicationPerformance Tests’, 2
UNION ALL Select ‘Tutorials’,’TutorialDocumentation’, 3
UNION ALL Select ‘Programmers’,’ProgrammDocumentation’, 3
UNION ALL Select ‘FAQ’,’Frequently AskedQuestions’, 4
UNION ALL Select ‘Forum’,’Forum’, 4
UNION ALL Select ‘Contact Us’,’Contact Us’, 4
UNION ALL Select ‘InternetRestrictions’,’Internet Restrictions’, 6
UNION ALL Select ‘Speed Solution’,’Speed Solutions’,6
UNION ALL Select ‘Application Center Test’,’Application Center Test Results’, 12
UNION ALL Select ‘Modem Results’,’Modem Results’,12

After inserting the data this is how my data looks like

Sql Data

Then the final query to get the desired output.

SELECT
parent.MenuId ParentId,
parent.MenuName Menu,
child.MenuId ChildId,
child.MenuName as SubMenu
FROM dbo.tblMenu parent
INNER JOIN tblMenu child ON parent.MenuId = child.ParentId;

This is how data looks now.

final query

Hope it will help somebody.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s