I have created a 'bottom-up' tree in my SQL database tables where each element has a column for a reference to its parent. Now I am trying to find out if there is an easy query to get all the parents of a child.
For example:
[ Parent #1 ]
|
[Parent #2] [Parent #3]
| |
[Child # 1] [Child #2] [Child #3] [Child #4]
I am giving Child #1 as the 'ID'. Each entry has reference to it's parent, and the topmost parent is null for parent id. So instead of in my code recursing and getting each parent, I was hoping there was a query I could do that would get Parent 1 开发者_如何学JAVAid, parent 2 id, and child 1 id by just providing child 1 id.
You'd want to use a hierarchical query, using the START WITH and CONNECT BY clauses. See this link.
It's just a Hierarchical Retrieval
I guessed a query , so improvements are welcome.
SELECT node_id
FROM tree
WHERE child_id="child_1"
START WITH id="child_1"
CONNECT BY PRIOR parent_id=child_id;
You also can use a nested set or adjacency model. Both have the advantage to work with any SQL database. The adjacency model works only good with a small limited number of nesting depth, while a nested set can be nested with an unlimited depth.
精彩评论