Example for http://stackoverflow.com/questions/8580003/select-root-node-from-subtree-postgresql-ltree-query-which-returns-several-de

nodes.sql

BEGIN;

CREATE EXTENSION IF NOT EXISTS LTREE;

/*==============================================================================
Define the tables.
==============================================================================*/

CREATE TABLE nodes
(
  id INTEGER PRIMARY KEY,
  parent_id INTEGER,
  ancestry LTREE NOT NULL,

  FOREIGN KEY (parent_id) REFERENCES nodes (id)
);

CREATE SEQUENCE serial_label_id START 1;

CREATE TABLE labels
(
  id INTEGER PRIMARY KEY DEFAULT NEXTVAL('serial_label_id'),
  node_id INTEGER NOT NULL,
  category VARCHAR(255),

  FOREIGN KEY (node_id) REFERENCES nodes (id)
);

/*==============================================================================
Populate the tables.
==============================================================================*/

INSERT INTO nodes (id, parent_id, ancestry)
VALUES
  (1, NULL, '1'),
  (2, 1, '1.2'),
  (3, 2, '1.2.3'),
  (4, 3, '1.2.3.4'),
  (5, 4, '1.2.3.4.5'),

  /* New tree */
  (6, NULL, '6'),
  (7, 6, '6.7'),
  (8, 7, '6.7.8'),

  /* Subtree from node (2, 1, '1.2') */
  (9, 2, '1.2.9'),
  (10, 9, '1.2.9.10')
;

INSERT INTO labels (node_id, category)
VALUES
  (1, 'One'),
  (3, 'Two'),
  (5, 'Two'),
  (9, 'Two'),
  (10, 'Three'),
  (6, 'Two')
;

/*==============================================================================
Query the data.
==============================================================================*/

/*******************************************************************************
These are the "id"s of the two trees, with the nodes having category='Two'
inside boxes:

      1         +---+
      |         | 6 |
  +-- 2 --+     +---+
  |       |       |
+---+   +---+     7
| 3 |   | 9 |     |
+---+   +---+     8
  |       |
  4       10
  |
+---+
| 5 |
+---+

*******************************************************************************/



/*******************************************************************************
This query functions as follows:

1. Select all the nodes with category='Two'
  (o) 3
  (o) 5
  (o) 6
  (o) 9

2. Including the roots, select all the subtrees branching from the selected
nodes in query 1. (with the selected nodes being the roots)
  (o) 3 -- 4 -- 5
  (o) 5
  (o) 6 -- 7 -- 8
  (o) 9 -- 10

3. Discard all the nodes from query 1. which have parents (NOTE: parents) in the
subtrees of query 2.
  (o) 3 inherits from 2, but 2 is not returned from query 2., so keep 3
  (o) 5 inherits from 4, which is returned from query 2., so discard 5
  (o) 6 does not inherit from any node, so keep 6
  (o) 9 inherits from node 2, but 2 is not returned from query 2., so keep 9

4. Return the remaining nodes:
  (o) 3
  (o) 6
  (o) 9
*******************************************************************************/
WITH roots AS
(
  SELECT n.*
  FROM nodes AS n
  JOIN labels AS l
    ON l.node_id = n.id
  WHERE
    l.category = 'Two'
)
SELECT roots.*
  FROM roots
WHERE NOT EXISTS
(
  SELECT 1
    FROM roots AS ancestors
  WHERE ancestors.ancestry @> roots.ancestry
    AND ancestors.id <> roots.id
);

ROLLBACK;