SQL refer Primary Key in same Table and Query

I am maintaining a table of Items (Id is Primary Key). Some Items have Parent Item ( Which is also an Item, the colum Parent holds the Id of the parent Item, which is also in the same table. Some Items does not have parent so the value is set to null.

Is there any method or proper design pattern to maintain such an information. (like: The foreign key is also from the same table)

If an Id of an Item is given, What is the recommended way to write a query which loop until the parent of item is null.

eg: Given value is 5, the query should return 2.

Parent of 5 is 4, parent of 4 is 3, parent of 3 is 2 and 2‘s parent is null

|---------------------|------------------|
|          Id         |       Parent     |
|---------------------|------------------|
|           1         |          4       |
|---------------------|------------------|
|           2         |       null       |
|---------------------|------------------|
|           3         |          2       |
|---------------------|------------------|
|           4         |          3       |
|---------------------|------------------|
|           5         |          4       |
|---------------------|------------------|
|           6         |       null       |

I am working on a PostgreSQL database, but I beleive the solution is generic and may support SQL Server, MySQL, SQLite or Oracle

Answer

You query hierarchic tables with recursive queries:

with cte(id, parent) as
(
    select id, parent from mytable where id = :id -- <== the starting ID here
    union all
    select m.id, m.parent from cte join mytable m on m.id = cte.parent
)
select id from cte where parent is null;

Advertisement

Answer