
CREATE TABLE arcs (source_id INT, target_id INT);

DELETE FROM arcs;

INSERT INTO arcs (source_id,target_id) VALUES
(1,2),
(1,3),
(1,4),
(2,5),
(2,6),
(2,7),
(3,2),
(3,4),
(3,6),
(3,7),
(3,8),
(3,9),
(4,8),
(4,9),
(4,10),
(5,6),
(7,8),
(9,10),
(8,11),
(10,11);


--
-- flat level query
--
SELECT target_id FROM arcs WHERE source_id in ( 1 );
-- returns 2,3,4

SELECT target_id FROM arcs WHERE source_id in (2,3,4);


--
-- nested query
--
  SELECT target_id FROM arcs WHERE source_id in (1)
UNION
  SELECT target_id FROM arcs WHERE source_id in
    ( SELECT target_id FROM arcs WHERE source_id in (1))
ORDER BY target_id;
-- returns all but 1 and 11


--
-- nested query
--
  SELECT target_id FROM arcs WHERE source_id in (1)
UNION
  SELECT target_id FROM arcs WHERE source_id in
    ( SELECT target_id FROM arcs WHERE source_id in (1))
UNION
  SELECT target_id FROM arcs WHERE source_id in
    (SELECT target_id FROM arcs WHERE source_id in
      ( SELECT target_id FROM arcs WHERE source_id in (1)))
ORDER BY target_id;
-- all but 1


--
-- recursive with query:  all descendants of the node with id = 1
--
WITH RECURSIVE pair(s,t) AS (
  SELECT source_id as s, target_id as t FROM arcs WHERE source_id = 2
UNION
  SELECT source_id, target_id FROM pair, arcs WHERE source_id = t)
SELECT DISTINCT t FROM pair ORDER BY t ;

-- starting FROM source_id = 1 will return all nodes except id = 1

-- sarting FROM source_id =2 will return 5, 6, 7, and 8 return 5, 6, 7, and 8
-- starting FROM source_id = 3 will return all but 1 and 3






