title: Oracle CONNECT BY with multiple tables
body: <p>I have 4 tables containing my data:</p>
  
  <pre><code>Table COMP: definition of my component data
  COMPID  | NAME      | DESCRIPTION
  --------+-----------+------------
  000123  | Comp. 1   | A44.123
  000277  | Comp. 2   | A96.277
  000528  | Comp. 3   | 1235287
  001024  | Comp. 4   | Lollipop
  004711  | Comp. 5   | Yippie
  
  Table COMPLIST: containing the sub-components of each component
  COMPID  | POS  | SUBCOMPID   | QUANTITY
  --------+------+------------ +-----------
  000123  | 1    | 000277      | 3
  000123  | 2    | 000528      | 1
  000528  | 1    | 004711      | 1
  
  Table COMPSUPPLIER: definition of the components suppliers
  COMPID  | SUPPLIER  | ORDERNUMBER
  --------+-----------+-------------
  000123  | Supp1     | A44.123
  000277  | Supp1     | A96.277
  000528  | Supp2     | 1235287
  001024  | Supp2     | ux12v39
  004711  | Supp1     | 123456
  
  Table ASSEMBLY: definition of my assembly
  ASSYID  | POS  | COMPID  | QUANTITY
  --------+------+---------+----------
  5021    | 1    | 000123  | 1
  5021    | 2    | 001024  | 2
  </code></pre>
  
  <p>I want to get all components used in an assembly with their supplier and order number (Edited: added Position):</p>
  
  <pre><code>POS    | COMPID  | NAME    | SUPPLIER | ORDERNUMBER | QUANTITY
  -------|---------+---------+----------+-------------+----------
  1      | 000123  | Comp. 1 | Supp1    | A44.123     | 1
  1.1    | 000277  | Comp. 2 | Supp1    | A96.277     | 3
  1.2    | 000528  | Comp. 3 | Supp2    | 1235287     | 1
  1.2.1  | 004711  | Comp. 5 | Supp1    | 123456      | 1
  2      | 001024  | Comp. 4 | Supp2    | ux12v39     | 2
  </code></pre>
  
  <p>My idea was to use a SELECT in combination with CONNECT BY but I can't get it working right.</p>
  
  <p>My current approach (Edited: updated with GurV's input):</p>
  
  <pre><code>SELECT c.COMPID, c.NAME, cs.SUPPLIER, cs.ORDERNUMBER
  FROM COMP c
  JOIN COMPSUPPLIER cs ON c.COMPID = cs.COMPID
  WHERE c.COMPID in (
      SELECT COMPID
      FROM ASSEMBLY
      WHERE ASSYID = '5021'
      UNION ALL
      SELECT SUBCOMPID
      FROM COMPLIST
      CONNECT BY NOCYCLE PRIOR SUBCOMPID = COMPID
      START WITH COMPID in (
        SELECT COMPID
        FROM ASSEMBLY
        WHERE ASSYID = '5402')
  );
  </code></pre>
  
  <p>With this I get all my sub components but not the position. Is it possible to get also the position column somehow?</p>