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
It does not matter if you have separate table.
Here is the query
,(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 Name From Category Where CategoryID In (C.ParentCategoryID)) As ‘Parent’
FROM [MetroShirts].[dbo].[Category] C
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