title: Joining tables and recursive query
body: <p>I have following tables.
  Articles, Categories, ArticleCategories</p>
  
  <p>Each article can have multiple categories.
  The problem is that database is designed in a way where Category contains ParentCategoryId, and article does not need to be connected with a root category, but actually is part of it, since it belongs to one of its child categories.</p>
  
  <p>Example</p>
  
  <p>Categories</p>
  
  <ol>
  <li>CategoryId 1, ParentCategory 0 </li>
  <li>Category2, ParentCategory 1 </li>
  <li>Category 3, ParentCategory 2</li>
  </ol>
  
  <p>Article</p>
  
  <ol>
  <li>Id = 1</li>
  </ol>
  
  <p>ArticleCategories</p>
  
  <ol>
  <li>ArticleId = 1, CategoryId = 3</li>
  </ol>
  
  <p>Now, what i need is a way to list articles with their root category (categories which have ParentID = 0).
  Returned dataset would have ArticleID and RootCategoryId (no need for child categories)</p>
  
  <p>I started with</p>
  
  <pre><code>SELECT 
  ar.Id, 
  ar.Title, 
  ac.CategoryId,
  ca.Name FROM Article ar
  INNER JOIN ArticleCategories ac
  ON ar.Id = ac.ArticleId
  INNER JOIN Categories ca
  ON ca.CategoryId = ac.CategoryId
  </code></pre>
  
  <p>But this is obviously just a query to join article with existing categories, not their root categories.
  I guess i need some kind of recursion to reach root category.</p>