title: MySQL - Adjacency List Model - Get Depth
body: <p>I have an organization table that has an <code>id</code>, a <code>parent_id</code>, and a <code>name</code> column. There are roughly 50k rows in this table. There is just one top level parent and the rest are all under that. In Oracle, I am able to easily retrieve the current depth of a particular organization with the <code>level</code> pseudocolumn:</p>
  
  <pre><code>SELECT id, parent_id, level, name
  FROM organizations
  START WITH parent_id = 1
  CONNECT BY PRIOR id = parent_id
  </code></pre>
  
  <p>I am at a loss of what the proper way to do the above in MySQL is. I need to fetch the entire tree along with the node's depth in one query.</p>
  
  <p>There are a plethora of questions on StackOverflow that have to do with this, but none of them seem to have a really good answer to it, mostly links to blogs with dubious solutions. Surely this is doable in some sort of straight-forward manner?</p>
  
  <p>Unfortunately modifying the table in any way is not an option, so nested sets is not a possibility.</p>