0

I have a table with parent-child relations. The relations can go n-level deep. There is also a table with elements that belong to a group.

CREATE TABLE group_children(
  id serial PRIMARY KEY,
  parent_id integer,
  children_id integer,
  contains boolean
);

CREATE TABLE group_item(
  id serial PRIMARY KEY,
  group_id integer,
  name text
);

INSERT INTO group_children(parent_id, children_id, contains) VALUES
  (1, 2, true),
  (1, 3, false),
  (2, 4, true),
  (2, 5, false),
  (3, 6, true),
  (3, 7, false);

INSERT INTO group_item(group_id, name) VALUES
  (4, 'aaa'),
  (4, 'bbb'),
  (5, 'bbb'),
  (5, 'ccc'),
  (6, 'aaa'),
  (6, 'bbb'),
  (7, 'aaa'),
  (7, 'ccc');

So, we can represent this data as enter image description here It is not necessary to be in the form of a binary tree, just a simple case. Group can contains m child.

Need to read right to left. Group 4 contains ['aaa', 'bbb'], group 5 - ['bbb', 'ccc']. Group 2 includes all items from group 4 and excludes from group 5. So group 2 contains ['aaa']. And so on. After all computation group 1 will contain ['aaa'].

Question is: how to build a sql query to get all items that belong to the group 1?

All i could do:

WITH RECURSIVE r AS (
    SELECT group_children.parent_id, group_children.children_id, group_children.contains, group_item.name
    FROM group_children
    LEFT JOIN group_item ON group_children.children_id = group_item.group_id
    WHERE parent_id = 1

    UNION ALL

    SELECT group_children.parent_id, group_children.children_id, group_children.contains, group_item.name
    FROM group_children
    LEFT JOIN group_item ON group_children.children_id = group_item.group_id
    JOIN r ON group_children.parent_id = r.children_id
)
SELECT * FROM r;

SQL Fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.