title: How to find all paths recursively from relational table in SQL Server using transitive property?
body: <p>I would like to be able to recursively find all relationships in a table. I have a relational table, and essentially I would like to apply the transitive property (i.e. if A~B, B~C =&gt; A~C), and with the newly found relationship(s), I would like to recursively do this until there are no more paths to be found. The data is all in SQL-Server. I have found a few other posts and tried to emulate the solutions provided with recursion. Maybe I am just not understanding how to implement my need?</p>
  <p>To give some context:</p>
  <p><a href="https://i.stack.imgur.com/JsbIM.png" rel="nofollow noreferrer">Example contents of the relational table on SQL</a></p>
  <p><a href="https://i.stack.imgur.com/d1fg0.png" rel="nofollow noreferrer">A View on SQL that makes the relation multidirectional</a></p>
  <p><a href="https://i.stack.imgur.com/PlQem.png" rel="nofollow noreferrer">What I would like to derive</a></p>
  <p>The relational table is relating the table that the IDs come from to itself, which is why I created the multidirectional view. It made it easier to join on <code>ID</code>, and <code>SELECT ID2</code>.</p>
  <p>Consider my table is just called <code>relatedIDs</code>, my view is called <code>relatedView</code>, and the derived view I'd like to make is called <code>derivedView</code>, then pulling from another post I tried something like this:</p>
  <pre><code>WITH RECURSIVE derivedView AS
  (
    SELECT ID
    FROM relatedView
    UNION ALL
    SELECT r.ID, 
    FROM derivedView d, relatedView r
    WHERE r.ID2 = d.ID
  )
  SELECT * FROM derivedView;
  </code></pre>
  <p>However I am unable to really even test this as I have &quot;Invalid Syntax near <code>derivedView</code>. Expecting '(', or AS&quot; on the very first line.</p>
  <p>As Requested SQL of creating tables and views:</p>
  <pre><code>/* RELATIONAL TABLE 
  ID  | ID2
  ---------
  213 | 404
  404 | 605
  */
  CREATE TABLE relatedIDs (
    [ID] [int] NOT NULL,
    [ID2] [int] NOT NULL,
    PRIMARY KEY (ID, ID2)
  );
  INSERT INTO relatedIDs VALUES (213, 404);
  INSERT INTO relatedIDs VALUES (404, 605);
  
  
  /* MULTIDIRECTIONAL VIEW
  ID  | ID2
  ---------
  213 | 404
  404 | 213
  404 | 605
  605 | 404
  */
  SELECT relatedIDs.ID, relatedIDs.ID2
  FROM relatedIDs
  UNION
  SELECT relatedIDS.ID2 AS ID, relatedIDs.ID
  FROM relatedIDs
  
  
  /* What I'd Like to Derive: 
  ID  | ID2
  ---------
  213 | 404
  404 | 213
  404 | 605
  605 | 404
  213 | 605
  605 | 213
  */
  </code></pre>