Click here to Skip to main content
13,863,239 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
this is my table
id   |  Parent_id  |  Category
1    |	0	   | Clothes
2    |	1	   | Women
3    |	2	   | Jewellery
4    |	0	   | Mobiles


What I have tried:

by select query i need to display like below

id   |  Parent_id  |  Category  |  Parent_name
1    |	0	        | Clothes    |  none
2    |	1	        | Women      |  Clothes
3    |	2	        | Jewellery  |  Women
4    |	0	        | Mobiles    |  none
Posted
Updated 10-Feb-19 23:23pm
Comments
CHill60 11-Feb-19 3:51am
   
And what query have you tried to create to achieve those results?
Error Kindness 11-Feb-19 4:22am
   
Your table is kinda confusing.

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Check this:

DECLARE @hierarchy TABLE (id INT IDENTITY(1,1), Parent_id INT, Category NVARCHAR(50))

INSERT INTO @hierarchy (Parent_id, Category)
VALUES(0, 'Clothes'),
(1, 'Women'),
(2, 'Jewellery'),
(0, 'Mobiles')


SELECT a.id, a.Parent_id, a.Category, ISNULL(b.Category, 'none') As Parent_Name
FROM @hierarchy AS a LEFT JOIN @hierarchy AS b ON a.Parent_id = b.id 


I'd strongly recommend to read this: Visual Representation of SQL Joins[^]
   
Comments
Member 14024377 11-Feb-19 6:42am
   
Thanks
Maciej Los 11-Feb-19 7:14am
   
You're very welcome.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01 | 2.8.190214.1 | Last Updated 11 Feb 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100