title: Unable to construct appropriate linq-to-sql query
body: <p>I'm having trouble coming up with the right linq to sql query for the problem below.</p>
  
  <p>I have 3 tables:</p>
  
  <p>A) "Product" with the following columns:</p>
  
  <ul>
  <li>productID: unique identifier</li>
  <li>pageID: identifier of the page for the product</li>
  <li>companyID: identifier of the company that owns the product</li>
  </ul>
  
  <p>B) "Company" with the following columns:</p>
  
  <ul>
  <li>companyID: unique identifier of the company </li>
  <li>pageID: identifier of the page for the company</li>
  </ul>
  
  <p>C) "Page" with  the following columns:</p>
  
  <ul>
  <li>pageID: unique identifier of the page </li>
  <li>parentID: id of the parent page (basically, a company can own another company, and this is where it is maintained)
  <img src="https://i.stack.imgur.com/5sxZ6.png" alt="SQL diagram"></li>
  </ul>
  
  <p>One thing is that the <em>companyID</em> in the <strong>Product</strong> table is equal to the <em>pageID</em> in the <strong>Company</strong> table</p>
  
  <p>This is mainly to know the URL of the company that owns the product (FYI, this is the schema I have to work with, so I can't really modify it unfortunately).</p>
  
  <p>What I'm trying to achieve is:
  The user will have a form and will enter the name of a company in a textbox, and I need to be able to retrieve the list of products that are owned by that company.</p>
  
  <p>I'm able to do that when there are no parent companies:</p>
  
  <pre><code>from p in ctx.Products
  join c in ctx.Companies on p.pageID equals c.companyID
  where ...
  select ...
  </code></pre>
  
  <p>However, if a company has a parent (defined in the Page table), I'm not sure how to go about that? Is that another join statement??</p>