title: Recursive relationship on a many to many table
body: <p>I set up a many to many recursive table like this:</p>
  
  <pre><code>CREATE TABLE PROD
  (
  IDPROD INT NOT NULL PRIMARY KEY,
  NAME VARCHAR(3)
  );
  
  CREATE TABLE COMP
  (
  IDPARENT INT REFERENCES PROD(IDPROD),
  IDCHILD INT REFERENCES PROD(IDPROD)
  );
  
  INSERT INTO PROD (IDPROD, NAME) VALUES (1, 'abc');
  INSERT INTO PROD (IDPROD, NAME) VALUES (2, 'def');
  INSERT INTO PROD (IDPROD, NAME) VALUES (3, 'ghi');
  INSERT INTO PROD (IDPROD, NAME) VALUES (4, 'jkl');
  INSERT INTO PROD (IDPROD, NAME) VALUES (5, 'mno');
  
  INSERT INTO COMP (IDPARENT, IDCHILD) VALUES (1, 2);
  INSERT INTO COMP (IDPARENT, IDCHILD) VALUES (3, 4);
  INSERT INTO COMP (IDPARENT, IDCHILD) VALUES (4, 5);
  </code></pre>
  
  <p>With a recursivs CTE I can get all the children of a specific node from the second table.</p>
  
  <pre><code>WITH RECURSIVE TEST (IDPARENT, IDCHILD) AS
  (SELECT P0.IDPARENT, P0.IDCHILD
  FROM COMP AS P0
  WHERE P0.IDPARENT = 3
  UNION ALL
  SELECT P1.IDPARENT, P1.IDCHILD
  FROM COMP AS P1, TEST AS T
  WHERE T.IDCHILD = P1.IDPARENT)
  
  SELECT * FROM TEST
  </code></pre>
  
  <p>But I need a query that will give me the entire structure, not just for one node. Like in the classic adjacency list where you get all the root nodes where IDPARENT IS NULL and their children listed below. I use Firebird.</p>