[CS-FSLUG] SQL Query Question

[Don Parris]

On Thu, Aug 4, 2011 at 11:41, Robert Wohlfarth <rbwohlfarth at gmail.com>wrote:

> 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)...
>
> WITH RECURSIVE cat_iwant(category_id, parent_id) AS (
>
> SELECT category_id, parent_id
>
> FROM tblcategory
>
> WHERE category_name = 'category_name'
>
> UNION ALL
>
> SELECT c.category_id, c.parent_id
>
> FROM tblcategory c, cat_iwant cw
>
> WHERE c.category_id = cw.parent_id
>
> )
> SELECT
>
> (
>
> SELECT category_id
> FROM cat_iwant
> WHERE parent_id IS NULL
>
> ) AS parent_id,
>
> SUM(dtl.amount)
>
> FROM
>
> cat_iwant cw
> INNER JOIN (
>
> SELECT category_id, SUM(amount) AS amount
>
> FROM tbltransdetails
>
> GROUP BY category_id
>
> ) dtl ON dtl.category_id = cw.category_id
>
>
> 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.
>
> 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.
>
> Thanks Robert, that's much closer.  Now I get an empty result consisting of
"parent_id" and "sum".  :-)

I'll keep tinkering with it, and let you know some of the things I try out.

Blessings,
Don
-- 
D.C. Parris, FMP, LEED AP O+M, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
https://www.xing.com/profile/Don_Parris  |
http://www.linkedin.com/in/dcparris
GPG Key ID: F5E179BE
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://ofb.biz/pipermail/christiansource_ofb.biz/attachments/20110804/622c01fe/attachment.htm>