How to get 'linked' items in an SQL query

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.