title: How can I make this parent-child relationship work?
body: <p>Using MySQL how can I make this hierarchy work?</p>
  
  <ul>
  <li>Parent's ID is 100. This Parent has a ParentID of 0.</li>
  <li>Child has an ID of 101. The ParentID is 100.</li>
  <li>SubEntity has an ID of 105.  The ParentID is 100.</li>
  <li>Child of Subentity has an ID of 106.  Their ParentID is 105.</li>
  </ul>
  
  <p>This query will be plugged into iReport.  Currently the Subentity and it's children do not roll up into the Parent.</p>
  
  <p>This is what I ended up going with:</p>
  
  <pre><code>`Select
  case
  when FC.ParentType = 'PARENT' then FC.FundCode
  when FB.ParentType = 'PARENT' then FB.FundCode
  when F.ParentType = 'PARENT' then F.FundCode
  else 0 end as `ParentID`,
  case
  when FB.ParentType = 'SUBFUND' then FB.FundCode
  when F.ParentType = 'SUBFUND' then F.FundCode
  else 0 end as `SubfundID`,
  case
  when FB.ParentType = 'CHILD' then FB.FundCode
  when F.ParentType = 'CHILD' then F.FundCode
  else 0 end as `Children`,            
  F.FundName     
  From Fund F
  join Fund FB on F.ParentId = FB.FundCode
  join Fund FC on FB.ParentID = FC.FundCode`
  </code></pre>