<div class="gmail_quote">On Thu, Aug 4, 2011 at 8:09 AM, Don Parris <span dir="ltr"><<a href="mailto:parrisdc@gmail.com">parrisdc@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div class="gmail_quote"><div>WITH RECURSIVE cat_iwant(original_category, current_category, parent_category) AS<br>  (SELECT original_category, current_category, parent_category FROM tblcategory <br>  WHERE current_category = 'category_name'<br>

UNION ALL<br>SELECT c.original_category, c.current_category, c.parent_category<br>    FROM cat_iwant cw, tblcategory c<br>    WHERE c.category_name = cw.original_category)<br>SELECT <br>    cats.current_category,<br>    SUM(tbltransdetails.amount)<br>

FROM <br>    tbltransdetails <br>    INNER JOIN cats ON <br>        cats.original_category = tbltransdetails.category_id<br>WHERE cats.parent_category IS NULL<br>GROUP BY cats.current_category;<br></div></div></blockquote>
</div><div><br></div><div>I have limited experience using WITH. It was one of those things tucked in the back of my mind as "being useful someday". With that caveat, here's some experimental code (i.e. I haven't actually run it)...</div>
<div><br></div><div>WITH RECURSIVE cat_iwant(category_id, parent_id) AS (</div><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;">
<div>SELECT category_id, parent_id </div></blockquote></blockquote><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;">
<div>FROM tblcategory </div></blockquote></blockquote><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;">
<div>WHERE category_name = 'category_name'</div></blockquote></blockquote><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><div>UNION ALL</div></blockquote><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;">
<blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><div>SELECT c.category_id, c.parent_id</div></blockquote></blockquote><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;">
<blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><div>FROM tblcategory c, cat_iwant cw</div></blockquote></blockquote><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;">
<blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><div>WHERE c.category_id = cw.parent_id</div></blockquote></blockquote><div>)</div><div>SELECT </div><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;">
<div>(</div></blockquote><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;">
<div>SELECT category_id </div><div>FROM cat_iwant </div><div>WHERE parent_id IS NULL</div></blockquote>) AS parent_id,</blockquote><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;">
<div>SUM(dtl.amount)</div></blockquote><div>FROM</div><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><div>cat_iwant cw</div><div>INNER JOIN (</div></blockquote><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;">
<blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><div>SELECT category_id, SUM(amount) AS amount</div></blockquote></blockquote><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;">
<blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><div>FROM tbltransdetails</div></blockquote></blockquote><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;">
<blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><div>GROUP BY category_id</div></blockquote>) dtl ON dtl.category_id = cw.category_id</blockquote><div><br></div><div>The WITH statement builds an in-memory table listing all of the categories in a given lineage. The top category has no parent (parent_id IS NULL). The recursive part of the WITH stops because no category_id matches a null parent_id.</div>
<div><br></div><div>The INNER JOIN of the main SELECT grabs all of the details for any category listed in the in-memory table from WITH. In plain English, it adds up all of the detail records for all of the categories in the lineage. </div>
<div><br></div>-- <br>Robert Wohlfarth<br><br>