sql query parent child

If  we need to display the child along with their parents in front of them , if there is no parent we need to display no parent or null.

Or another scenario is the most asked question in interviews is display employee names along with their manager names in front of the employees

My table is having same parentid column in category table

CategoryID

ParentCategoryID

Name

It does not matter if you have separate table.

Here is the query

SELECT C.[CategoryID]
,C.[ParentCategoryID]
,C.[Name]
,(Case When C.ParentCategoryID <> 0 Then (select Name From Category Where CategoryID = C.ParentCategoryID) else ‘No Parent’ End) As [Parent]
FROM [Category] C

Another way to achieve this is

SELECT C.[CategoryID]

,C.[Name]
,C.[ParentCategoryID]
, (Select Name From Category Where CategoryID In (C.ParentCategoryID)) As ‘Parent’
FROM [MetroShirts].[dbo].[Category] C

 

 

Parent Child SQL Query
Parent Child SQL Query

Here is another version when parent child relation is in two tables

Select PC.ProductCategoryID , PSC.ProductSubcategoryID , PSC.Name , PC.Name as [Parent] from AdventureWorks2008.Production.ProductCategory PC
Inner join AdventureWorks2008.Production.ProductSubcategory PSC
ON PC.ProductCategoryID = PSC.ProductCategoryID

SQL query parent child relation ship
SQL query parent child relation ship