title: SQL To Pull Linked List(ish) Data From Tables
body: <p>Thank you in advance for your help... </p>
  
  <p>I want to get the parent site list/informatoin for a site based on its ID.  There is a site hierarchy described in 2 tables, like this:</p>
  
  <pre><code>table: site
  - id  (PK)
  - name
  
  table: hierarchy
  - siteid   (PK) (siteid   =&gt; site.id)
  - parented (PK) (parented =&gt; site.id)
  - topid         (topid    =&gt; site.id)
  </code></pre>
  
  <p>For example, you may have the following data in the tables:</p>
  
  <pre><code>site:
  id,name
  0,Earth
  1,US
  2,NY
  3,GA
  4,Queens
  5,Farmers' Market
  6,Buckhead
  
  hierarchy:
  siteid,parented,topid
  5,4,1
  4,2,1
  2,1,1
  1,0,1
  5,6,1
  6,3,1
  3,1,1
  1,0,1
  </code></pre>
  
  <p>I want to get all the parents of the Farmers's Market sites.  There are markets in both Buckhead and Queens.  </p>
  
  <p>How do I make SQL walk / recurse up the tree to get something like this if I want the parent list for site id=5?:</p>
  
  <pre><code>site_name,parent_name
  Farmers' Market,Queens
  Queens,NY
  NY,Earth
  Farmers' Market,Buckhead
  Buckhead,GA
  GA,Earth
  </code></pre>
  
  <p>It occurs to me that there may need to be more than one row in the site table for Farmers' Market, but, I don't think so... </p>
  
  <p>Thanks for your help,</p>
  
  <p>David </p>