1

5 and have the following scenario:

I have a database table with two columns: id(int), parentId(int).

Lets say my data looks like this

id  parentId
1   null
2   1
3   1
4   3
5   3
6   4
7   4
8   6

Now I would like to write a query which list all elements which have a specific element as ancestor. How can this be done?

For example I want all elements that have 4 as ancestor; 6,7,8.

Lightness Races in Orbit
  • 378,754
  • 76
  • 643
  • 1,055
laitinen
  • 153
  • 1
  • 7
  • 1
    possible duplicate of [mysql php question get parent id recrusive](http://stackoverflow.com/questions/4006974/mysql-php-question-get-parent-id-recrusive) – Lightness Races in Orbit Jan 11 '12 at 15:11
  • Reformatted for code, but there are sections that don't make sense: it looks like there is text missing before `5 and have the following`? – msanford Jan 11 '12 at 15:11
  • Duplicate of: http://stackoverflow.com/questions/4345909/mysql-recursion – jwl Jan 11 '12 at 15:12
  • and http://stackoverflow.com/a/192462/560648 – Lightness Races in Orbit Jan 11 '12 at 15:12
  • 2
    A fully recursive query is not possible in MySQL using a single query. You can simulate it for a specified number of levels using self-joins, but a generalized single-query solution isn't possible. Other DBMSs have a "connect by prior" type thing which does allow single-query recursion. – Marc B Jan 11 '12 at 15:13

2 Answers2

0

Your table is an example of an adjacency list model of hierarchical data. This has well known limitations, including making it impossible to write a single query that does what you want. (You can do a single query by imposing a depth limit, but it's painful.)

A better structure for this would be a nested set model. There's a nice article here on how the nested set model works, including plenty of SQL code for implementing it.

Ted Hopp
  • 232,168
  • 48
  • 399
  • 521
0

MySQL lacks the hierarchical features other RDBMS have such as MSSQL common table expressions (CTE) and Oracle's connect by which make working with adjacency lists simple and efficient. As a consequence you'll have to do something like the following:

Generating Depth based tree from Hierarchical Data in MySQL (no CTEs)

Hope this helps :)

Community
  • 1
  • 1
Jon Black
  • 16,223
  • 5
  • 43
  • 42