title: SQL adjacency list query
body: <p>Trying to model dependencies on a service that is accessed by users. I have created a parent/child adjacency list style table that sets out that 'the service' relies on Component 1, which relies on Component 2, etc, etc. and the type of dependency e.g. full or partial </p>
  
  <p>This image shows the layout - Dependencies image</p>
  
  <p><a href="https://i.stack.imgur.com/piC3E.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/piC3E.jpg" alt="enter image description here"></a></p>
  
  <p>Comp 1 &amp; 9 are coloured differently because if they fail then overall service fails. If any of Comp 2-9 fail then the service continues but just has reduced resiliency.</p>
  
  <p>Here is what I have used to create the table</p>
  
  <pre><code>CREATE TABLE scratch
  ( 
      KeyID        int    PRIMARY KEY        NOT NULL, 
      CompDesc       varchar(30), 
      CompID        int    NOT NULL, 
      ReliesOn       int    NOT NULL, 
      RelianceType       varchar(30), 
  )
  
  INSERT scratch SELECT 0, 'Service', 0, 1, 'Full'
  INSERT scratch SELECT 1, 'Component 1', 1, 2, 'Partial'
  INSERT scratch SELECT 2, 'Component 1', 1, 3, 'Partial'
  INSERT scratch SELECT 3, 'Component 1', 1, 4, 'Partial'
  INSERT scratch SELECT 4, 'Component 4', 4, 5, 'Full'
  INSERT scratch SELECT 5, 'Component 5', 5, 6, 'Full'
  INSERT scratch SELECT 6, 'Component 6', 6, 7, 'Partial'
  INSERT scratch SELECT 7, 'Component 6', 6, 8, 'Partial'
  INSERT scratch SELECT 8, 'Component 2', 2, 9, 'Full'
  INSERT scratch SELECT 9, 'Component 3', 3, 9, 'Full'
  INSERT scratch SELECT 10, 'Component 7', 7, 9, 'Full'
  INSERT scratch SELECT 11, 'Component 8', 8, 9, 'Full'
  </code></pre>
  
  <p>I can then run a very crude query to show the 4 different options the users can access the service -</p>
  
  <pre><code>SELECT t1.ReliesOn AS lev1, t2.ReliesOn as lev2, t3.ReliesOn as lev3, t4.ReliesOn as lev4, t5.ReliesOn as lev5, t6.ReliesOn as lev6
  FROM Scratch AS t1
  LEFT JOIN Scratch AS t2 ON t2.CompID = t1.ReliesOn
  LEFT JOIN Scratch AS t3 ON t3.CompID = t2.ReliesOn
  LEFT JOIN Scratch AS t4 ON t4.CompID = t3.ReliesOn
  LEFT JOIN Scratch AS t5 ON t5.CompID = t4.ReliesOn
  LEFT JOIN Scratch AS t6 ON t6.CompID = t5.ReliesOn
  WHERE t1.ReliesOn = 1;
  </code></pre>
  
  <p>(Apologies, there is probably a better way of stringing that query together)</p>
  
  <p>with this result -</p>
  
  <pre><code>lev1    lev2    lev3    lev4    lev5    lev6
  1       2       9       NULL    NULL    NULL
  1       3       9       NULL    NULL    NULL
  1       4       5       6       7       9
  1       4       5       6       8       9
  </code></pre>
  
  <p>What I am trying to do is create a query that I can set out the comp(s) that have failed and determine if the service is still available e.g.</p>
  
  <pre><code>Failed      Result
  1           No Service
  2,3         Reduced Resiliency
  3,8         Reduced Resiliency
  2,3,7,8     No Service
  </code></pre>
  
  <p>This is just a very simple example, I have many more that I need to add to this, and in the majority of cases a number of services will rely on the same components.</p>
  
  <p>So, how do you go from the failed comp and go up/across/down the dependencies to figure out if a path still exists?  </p>
  
  <p>Hopefully, this makes sense</p>
  
  <p>Thanks</p>