title: Running a SELECT query on a closure table with a JOIN?
body: <p>I have an app set up which has nested comments attached to posts. I decided to use the <a href="http://www.slideshare.net/billkarwin/models-for-hierarchical-data" rel="nofollow">closure table</a> method (slide 40) for the comments due to how cheap hard disk space is versus how easy it seems to be to query and manage the tree structure. However, I'm running into a problem. I can't seem to figure out how to grab the tree path based on the post ID, not the ancestor ID (slide 49).</p>
  
  <p>My database structure looks like this:</p>
  
  <pre><code>table: comment_paths
  --------------------
  parent_id (fk on comments.id)
  child_id  (fk on comments.id)
  depth
  
  table: comments
  ---------------
  id
  parent_id (fk on comments.id)
  post_id   (fk on posts.id)
  text
  
  table: posts
  ---------------
  id
  name
  </code></pre>
  
  <p>If I know the parent_id beforehand, as in the slideshow, it's simple to grab the tree:</p>
  
  <pre><code>SELECT c.*, p.*
  FROM comments AS c
  JOIN comment_paths AS p
  ON c.id = p.child_id
  WHERE p.parent_id = 1
  </code></pre>
  
  <p>However, I don't know the <code>parent_id</code> beforehand; I only know the <code>post_id</code>. The way the database is set up, there is more than one tree associated with a post:</p>
  
  <pre><code>                 [post]
  -----------------------------------------
  [comment]       [comment]       [comment]   depth: 0
      |                               |
   [reply]                         [reply]    depth: 1
    |   |
   [r] [r]                                    depth: 2
  </code></pre>
  
  <p>My initial solution was a query like this:</p>
  
  <pre><code>SELECT c.*, p.*
  FROM comments AS c
  JOIN comment_paths AS p
  ON c.id = p.child_id
  WHERE p.parent_id IN
      (SELECT id FROM comments WHERE parent_id IS NULL AND post_id = 6)
  </code></pre>
  
  <p>It returns the correct data, but I feel like a nested SELECT like that isn't correct. Is there a better way to do this?</p>
  
  <p>Thanks!</p>