[CS-FSLUG] SQL Query Question

[Robert Wohlfarth]

On Thu, Aug 4, 2011 at 8:09 AM, Don Parris <parrisdc at gmail.com> wrote:

> WITH RECURSIVE cat_iwant(original_category, current_category,
> parent_category) AS
>   (SELECT original_category, current_category, parent_category FROM
> tblcategory
>   WHERE current_category = 'category_name'
> UNION ALL
> SELECT c.original_category, c.current_category, c.parent_category
>     FROM cat_iwant cw, tblcategory c
>     WHERE c.category_name = cw.original_category)
> SELECT
>     cats.current_category,
>     SUM(tbltransdetails.amount)
> FROM
>     tbltransdetails
>     INNER JOIN cats ON
>         cats.original_category = tbltransdetails.category_id
> WHERE cats.parent_category IS NULL
> GROUP BY cats.current_category;
>

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.

-- 
Robert Wohlfarth
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://ofb.biz/pipermail/christiansource_ofb.biz/attachments/20110804/fc5a74e7/attachment.htm>