-2

I have a table:

Id | child | parent 
 1      67         0 
 2      69        67 
 3      79        68 
 4      76        69 
 7      75        68

I want to select records until parent id is zero

All records in same table

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Ankit
  • 7
  • 3
  • Please provide an output example and the code that you have tried. – Alex Jul 28 '17 at 06:53
  • select * from table_1 c,table_1 where c.ID=P.Parent and c.Parent !=0 – Ankit Jul 28 '17 at 06:57
  • Is it a tree? Please explain the semantics of attributes. Output example for your data would be useful. – Radim Bača Jul 28 '17 at 06:57
  • Maybe this is helpful? https://stackoverflow.com/questions/7631048/connect-by-prior-equivalent-for-mysql – Klas Lindbäck Jul 28 '17 at 06:58
  • Yes it is tree But My query not work I am new to sql – Ankit Jul 28 '17 at 06:58
  • For inspiration, have a look at the Recursive Common Table Expressions on [this refman page](https://dev.mysql.com/doc/refman/8.0/en/with.html). Or [here](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) – LukStorms Jul 28 '17 at 07:33
  • Thanks for reply But I want ancestors of children and path from children id to top parent – Ankit Jul 28 '17 at 11:16
  • @Ankit Can you then please edit your question, explain that and include some expected output? – LukStorms Jul 28 '17 at 13:00

1 Answers1

1

To quote from How to represent a data tree in sql

some databases, particularly MySQL, have some issues in handling this model, because it requires an ability to run recursive queries which MySQL lacks.

A long but very thorough explanation of the problem (and its solution) can be found here: Managing hierarchical data in mysql

TL/DR: If you want to solve this problem with a single query then you need to change your tree to a nested list structure - which is a bit harder to understand but more efficient to handle in mysql.

Let's take this tree:

  • A
    • B
    • C
      • D
      • E
    • F

In your adjacency list format this would look like this

id | text | parent
1    A
2    B      1
3    C      1
4    D      3
5    E      3
6    F      1

Getting all parents of node D is not easy.

Now we convert this to a nested set:

0_________________A__________________11
  1_B_2   3_______C_______8   9_F_10
            4_D_5   6_E_7

id | text | lft | rgt
1    A      0     11
2    B      1     2
3    C      3     8
4    D      4     5
5    E      6     7
6    F      9     10

Now getting all parents of node D is easy:

SELECT p.* FROM nestedset p
INNER JOIN nestedset o ON o.lft > p.lft AND o.rgt < p.rgt
WHERE o.text == "D"

As for how to do other operations, follow the link i posted.

Kempeth
  • 1,856
  • 2
  • 22
  • 37