CODEWITHSUNDEEP
sql-server-2008
Sunny Sandeep
Sunny Sandeep

Reputation: 1011

How to Select all Category with its specific Parent CategoryName

I am working on an application. where I have a Table:-

Category(Category_ID,CategoryName,Parent,Category_Tag)

In this table Category_ID and Parent of type bigint and CategoryName and Category_Tag are of nvarchar. I want to select all CategoryName from Category table with its Name and Parent Name also. If any Category does not have any Parent then the query should return '-'

I have used following sql query:

SELECT Category_ID, Category_Name, Parent, Category_Tag FROM Category

But dows not getting the desired result.Please help me.

Upvotes: 1

Views: 53

Answers (2)

valex
valex

Reputation: 24144

If you need only ONE level parent category then use LEFT JOIN

SELECT a.Category_ID, a.Category_Name, a.Parent, a.Category_Tag,
       ISNULL(b.Category_Name,'-') as Parent_Name
FROM Category as a
LEFT JOIN Category as b on (a.Parent=b.Category_ID)

Upvotes: 1

Hitesh
Hitesh

Reputation: 3498

If you want the empty parent to be replaced with '-', use ISNULL 

SELECT Category_ID, Category_Name, ISNULL(convert(varchar(20),Parent), '-') as Parent, Category_Tag FROM Category

Upvotes: 0

Related Questions