I have to retrieve parent-child by order

Question

1.00/5 (1 vote)

See more:

id |parent_id |name
-------------------------
1  |0         |Sports
2  |0         |Clothes   
3  |0         |Facial     
4  |2         |Mens 
5  |0         |Electronics
6  |2         |Shirts
7  |3         |Cream
8  |5         |Mobile
9  |8         |Apple

What I have tried:

id |parent_id |name
-------------------------
1  |0         |Sports
2  |0         |Clothes   
4  |2         |Mens 
6  |2         |Shirts
3  |0         |Facial
7  |3         |Cream     
5  |0         |Electronics
8  |5         |Mobile
9  |8         |Apple

Posted 11-Feb-19 21:08pm

Member 14024377

Updated 11-Feb-19 22:21pm

Maciej Los

v3

Add a Solution

Comments

OriginalGriff 12-Feb-19 3:16am

We are more than willing to help those that are stuck: but that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.

Member 14024377 12-Feb-19 3:27am

I tried below code.
But only first parent child is retrieving by order

select *
from Product_Category
order by
case
when parent_id = 0
then name
else (
select name
from Product_Category parent
where parent.id = Product_Category.parent_id
)
end
, case when parent_id = 0 then 1 end desc
, name

CHill60 12-Feb-19 3:32am

I don't normally push people towards one of my own articles but the example "Traversing a hierarchy" may be exactly what you want - Processing Loops in SQL Server[^]

Maciej Los 12-Feb-19 4:02am

Sounds like an answer to me ;)

CHill60 12-Feb-19 4:21am

Posted :-)

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2019-02-11T22:21:00

First of all, please follow the link provided by CHill60[^].

Try this:

SQL

DECLARE @hierarchy TABLE (id INT IDENTITY(1,1), parent_id INT, name NVARCHAR(50))
INSERT INTO @hierarchy(parent_id, name)
VALUES(0, 'Sports'),
(0, 'Clothes'),
(0, 'Facial'),
(2, 'Mens'),
(0, 'Electronics'),
(2, 'Shirts'),
(3, 'Cream'),
(5, 'Mobile'),
(8, 'Apple')

;WITH CTE AS
(
        --initial query
	SELECT id, parent_id, name, 1 AS distance, id as pid
	FROM @hierarchy 
	WHERE parent_id = 0
        --recursive part
	UNION ALL
	SELECT h.id, h.parent_id, h.name, distance + 1 AS distance, h.parent_id As pid
	FROM @hierarchy h INNER JOIN CTE c ON c.id = h.parent_id 
)
SELECT *
FROM CTE 
ORDER BY pid, distance

As you can see, i've used small trick to return data in a correct order:
- in the first loop:
     - i'm setting 1 as a distance
     - i'm getting [id] from @hierarchy table as [pid]
- in every next loop:
     - i'm calculating distance by adding 1
     - i'm getting [parent_id] from @hierarchy table as [pid]

CHill60 · Answer 2 · 2019-02-11T22:21:00

Solution 2

As per my comment, I'm not in the habit of posting links to my own articles but in this case I think the worked example "Traversing a hierarchy" could be what you need.

Work through the example and come back with your code if you are still having problems - Processing Loops in SQL Server[^]

Posted 11-Feb-19 22:21pm

CHill60

Comments

Maciej Los 12-Feb-19 4:25am

Good one!