2

table:

    id  parent_id
    --------------
    a_1 NULL
    a_2 NULL
    b_1 a_1
    c_1 b_1
    d_1 c_1

Now, I have id:d_1

How can I get a_1,b_1,c_1 in one SQL query? (d_1's ancestor ids)?

Jason
  • 15,017
  • 23
  • 85
  • 116
Koerr
  • 15,215
  • 28
  • 78
  • 108
  • 1
    You mean d_1's ancestor ids. d_1's parent id is c_1, but you want all of the ancestors of d_1. (You might want to edit your question to clarify this). This is something that SQL does not do very well (handling arbitrary levels of hierarchy), though someone who is an expert may have a solution. – Francis Upton IV Dec 26 '11 at 18:32
  • Do you have a fixed maximum recursion depth? – Albin Sunnanbo Dec 26 '11 at 18:32
  • 1
    good question @AlbinSunnanbo, I just went with a very literal meaning of the question. I guess with LEFT JOIN and [maximum recursion depth] number of JOINS one might sufficient flexibility. For an undefined number of parents... I'd really like to see how someone creates a single SQL statement for that – Nicolas78 Dec 26 '11 at 18:36
  • @AlbinSunnanbo no,not limit the recursion depth – Koerr Dec 26 '11 at 18:40
  • Hope you don't mind I clarified the title. – Francis Upton IV Dec 26 '11 at 18:45
  • @Nicolas78, It is doable with recursive CTE:s, but that is not available in mysql. – Albin Sunnanbo Dec 26 '11 at 18:46

2 Answers2

2
SELECT c.id, b.id, a.id 
FROM T 
INNER JOIN T AS c on T.parent_id=c.id 
INNER JOIN T AS b ON c.parent_id = b.id 
INNER JOIN T as a ON b.parent_id = a.id

assuming your table's named T. untested though. not sure what happens if d doesn't have three parents, probably you don't get a thing, might try LEFT JOIN for those cases to obtain NULL values for the great parents. Also, the last JOIN isn't really necessary because you could simply select b.parent_id instead of a.id but, you know, just to drive home the pattern ;)

Nicolas78
  • 5,124
  • 1
  • 23
  • 41
  • Note this assumes a fixed depth of the hierarchy. – Francis Upton IV Dec 26 '11 at 18:36
  • yea, see my comment above, I took the question at face value, which may be the uninteresting part; with LEFT JOINs however this changes at least to an only fixed MAXIMUM depth – Nicolas78 Dec 26 '11 at 18:36
  • no. but you could use as many JOINs as you maximally want to look up and use LEFT JOIN. No idea how to make this for an infinite number of parents... – Nicolas78 Dec 26 '11 at 18:38
1

I really don't think you can do this in a simple SQL statement, it's a fundamental limitation of the SQL language.

Here is a heinous stored procedure that does roughly what you want (for SQL Server). So you would need to do something like this.

Francis Upton IV
  • 19,322
  • 3
  • 53
  • 57