CODEWITHSUNDEEP
sqlpostgresqlcommon-table-expressionpostgresql-9.1
ldrg
ldrg

Reputation: 4501

CTE SQL query is recursing in two directions

I'm using Postgres 9.1 and I've got a table with a family tree hierarchy in it. This table is called parents and has two foreign keys in it, one for the parent and one for the child in the relationship. The following SQL query (mostly stolen from the Postgres documentation) works but traverses up the tree as well as down:

with recursive temp(child, parent, depth, path, cycle) as
        (select child, parent, 1, array[child], false
         from parents
         where parent = 149

         union all

         select parents.child, parents.parent, temp.depth + 1, path || parents.child, parents.child = any(path)
         from temp, parents
         where parents.child = temp.parent)
    select distinct c1.name as child_name, c2.name as parent_name
    from temp
    join people c1 on temp.child = c1.id
    join people c2 on temp.parent = c2.id;

Parent 149 is the root node of the traversal.

The output has one generation of children and all generations of ancestors of 149. Ideally the query would descend the family tree and have no generations of ancestors.

Upvotes: 1

Views: 941

Answers (1)

Andriy M
Andriy M

Reputation: 77667

Disclaimer: this answer was accepted before I realised that @wildplasser had already suggested the same in their comment to the question. (Didn't mean to steal someone else's ideas, sorry.)

If you want to build only the generations descending from the given parent, you should change this condition

parents.child = temp.parent

to this

temp.child = parents.parent

because it is the temp children that should be treated as parents (i.e. be matched against parents.parent) at the next iteration.

Upvotes: 1

Related Questions