Good day everyone. I have such a question. Let's say there is a table in which a tree structure is stored. Suppose there are Id - record id in the table and IdParent - parent id. How can I use the sql query to select the topmost parent of an entry with id = 10? Is it even possible to use such sql tools?

  • And what? - Chad
  • it is desirable that it is not dependent on the specific subd tools, only the means of the sql itself. Well, if without it, then sql server - Andy_Reed
  • In fact, it is necessary to determine which root node (pid = 0) the current element belongs to? - Opalosolo
  • yes right - Andy_Reed
  • Try Nested Sets, if the number of requests for adding / deleting records is significantly less compared to reading (that is, they will mostly read from the database, not write), then NS is an excellent solution for storing trees, and similar tasks are solved there very simply. - ReinRaus

3 answers 3

The ability to create recursive queries is from SQL 1999. This is possible using the WITH statement. In MS, Sql will look like this:

WITH rec AS ( SELECT * FROM MyTable WHERE Id = 10 UNION ALL SELECT mt.* FROM MyTable mt JOIN rec p ON mt.parentID = p.id ) SELECT ParentId FROM rec where Id = 10

If I'm not mistaken, this will not work in MySql. About Oracle - not in the know

    @ReinRaus - instead of NS, I would advise using the Materialized path ( PostgreSQL Ltree ). NS has large loads when moving a node with a large number of child elements, since for each element it is necessary to recalculate the left and right keys.

      According to the standard it seems impossible. For Oracle, PostgresSQL is an implementation of tree queries. In MS sql server'e it seems depends on the version, in the new it seems like an opportunity but not sure. According to the standard, it is possible only if you limit it with crutches with nested queries by nesting level, for example:

        select * from table as l0 left join table as l1 on(l1.parent_id = l0.id) left join table as l2 on(l2.parent_id = l2.id) left join table as l3 on(l3.parent_id = l1.id) where l0.parent_id is null and (l1.id = 10 or l2.id = 10 or l3.id = 10)