Recursivly get a Tree Via self joined table

Using other questions here and Postgresql documentation I've managed to build a many-to-many self joined table.

However adding a WHERE clause is giving me trouble.

Problem:

A Category can have many child categories, and many parent categories. Given a category.Id, I want to retrieve the category, the category children, children's children and so on.

Example: given this structure:

child_1
    child_11
        child_111
        child_112
            child_1121
    child_21
child_2

Given: a clause of id = child_11

Expected results:
child_11, child_111, child_112, child_1121,

Actual results: child_11, child_111, child_112

Here is my attempt: http://sqlfiddle.com/#!17/3640f/2

In case Sqlfiddle is down: https://www.db-fiddle.com/#&togetherjs=LhDjxfPHo6

Note: I don't care about duplicating the where clause, my application can handle that

Table structure:

CREATE TABLE Category(id SERIAL PRIMARY KEY, name VARCHAR(255));
CREATE TABLE Categories(parent_id INTEGER, child_id INTEGER, PRIMARY KEY(parent_id, child_id));

ALTER TABLE Categories ADD FOREIGN KEY (parent_id) REFERENCES category (id);
ALTER TABLE Categories ADD FOREIGN KEY (child_id) REFERENCES category (id);

Table data:

```
INSERT INTO Category(id, name) VALUES (1, 'parent_1');
INSERT INTO Category(id, name) VALUES (2, 'child_1');
INSERT INTO Category(id, name) VALUES (3, 'child_2');
INSERT INTO Category(id, name) VALUES (4, 'child_3');
INSERT INTO Category(id, name) VALUES (5, 'child_1_1');
INSERT INTO Category(id, name) VALUES (6, 'child_1_2');
INSERT INTO Category(id, name) VALUES (7, 'child_1_1_1');
INSERT INTO Category(id, name) VALUES (10, 'child_of_many');
INSERT INTO Category(id, name) VALUES (11, 'parent_1');
INSERT INTO Category(id, name) VALUES (12, 'parent_2');

INSERT INTO Categories(parent_id, child_id) VALUES (1, 2);
INSERT INTO Categories(parent_id, child_id) VALUES (1, 3);
INSERT INTO Categories(parent_id, child_id) VALUES (1, 4);
INSERT INTO Categories(paren

Recursive CTEs are notoriously hard to learn. Take a look at this fiddle to play with a working model.

The 2nd query in the CTE, or Common Table Expression, needs to reference the first query. This is where the "recursive" name comes from in CTE.

My fiddle has a single table, where each cat can only have a single parent, which is arguably a more common tree scenario. This makes it slightly easier to understand, in my opinion.

Anyway, the DDL consists of:

CREATE TABLE cats
(
    cat_id SERIAL
      PRIMARY KEY
  , cat_name VARCHAR(20) NOT NULL
  , parent_cat_id int NULL
);

ALTER TABLE cats ADD FOREIGN KEY (parent_cat_id) REFERENCES cats (cat_id);

INSERT INTO cats (cat_name, parent_cat_id)
VALUES ('garfield', NULL);

INSERT INTO cats (cat_name, parent_cat_id)
VALUES ('bertina', 1);

INSERT INTO cats (cat_name, parent_cat_id)
VALUES ('oletha', 2);

And the recursive CTE looks like:

;WITH RECURSIVE cats_hierarchy AS
(
  SELECT c1.cat_id
      , c1.cat_name
      , cast('' as varchar(20)) as parent_cat_name
      , c1.parent_cat_id
      , 1 as level
  FROM cats c1
  WHERE c1.parent_cat_id IS NULL

  UNION ALL

  SELECT c2.cat_id
      , c2.cat_name
      , c1.cat_name AS parent_cat_name
      , c2.parent_cat_id
      , c1.level + 1 AS level
  FROM cats c2
      INNER JOIN cats_hierarchy c1 ON c2.parent_cat_id = c1.cat_id
)
SELECT *
FROM cats_hierarchy;

Results look like:

╔═════════╦═══════════╦══════════════════╦════════════════╦═══════╗
║ cat_id ║ cat_name ║ parent_cat_name ║ parent_cat_id ║ level ║
╠═════════╬═══════════╬══════════════════╬════════════════╬═══════╣
║ 1 ║ garfield ║ ║ (null) ║ 1 ║
║ 2 ║ bertina ║ garfield ║ 1 ║ 2 ║
║ 3 ║ oletha ║ bertina ║ 2 ║ 3 ║
╚═════════╩═══════════╩══════════════════╩════════════════╩═══════╝

In the CTE the first query (before the UNION ALL) shows cats that are at the root. The 2nd part of the CTE (after the UNION ALL) gets cats related to the parent cat. Those returned results are then queried again by the CTE, generating a tree.

Let me know if that helps, or if you need further clarification.

StackExchange Database Administrators Q#260253, answer score: 5