0

Below is my table structure

Id FirstName ReportingTo

6 A

9 B 6

10 C 6

11 D 9

12 E 9

16 F 11

20 G 12

23 H 20

This is my table structure, I want an output that will give me a result from bottom to top hierarchy. Means if I put Id = 23 then it should be give - (20,12,9,6). As I am using MySQL 5.7 so With Recursive function I can't use.

select id, firstname, reporting_to from (select * from users order by
id) products_sorted, (select @pv := '23') initialisation where
find_in_set(reporting_to, @pv) and length(@pv := concat(@pv, ',',
id))

But it gives only a single row. I need the entire hierarchy from bottom to top.

Improve this question
4
  • This answer to this question regarding recursive queries also addresses how to do this on MySQL 5.x: stackoverflow.com/a/33737203/4517964 Commented Jul 11, 2022 at 15:09
  • Thanx for the reply, I have already used the code that you described, but it give top to bottom hierarchy, I need bottom to top. Sorry if anything wrong Commented Jul 11, 2022 at 15:12
  • 1
    @Andrew why would you need a recursion for that? Commented Jul 11, 2022 at 15:19
  • Just run a simple query in a loop. That's the most basic thing Commented Jul 11, 2022 at 15:21
Related questions
3
SQL tree traversal
1
Get tree of single item in MySQL hierachircal database
1
get tree structure data from mysql
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.