CODEWITHSUNDEEP
sql
Vibeeshan Mahadeva
Vibeeshan Mahadeva

Reputation: 7238

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

Upvotes: 0

Views: 480

Answers (2)

Andy Ho
Andy Ho

Reputation: 1064

mysql 8.0.26 , you can query like this:

ps. the table Name is Employees

  select     id,
             parent
  from       Employees
  where      parent = :id
  union all
  select     p.id,
             p.parent
  from       Employees p
  inner join cte
          on p.parent = cte.id
)
select * from cte;

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94969

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;

Upvotes: 1

Related Questions