[CS-FSLUG] SQL Query Question

[Don Parris]

You might take a look at the WITH statement:
http://www.postgresql.org/docs/8.4/static/queries-with.html.

>
>> I would try using WITH RECURSIVE to create a table of three columns:
>> original_category, current_category, parent_category. For example, the WITH
>> RECURSIVE would create this table:
>> Transportation | Fuel | NULL
>> Transportation | Auto | Fuel
>> Transportation | Transportation | Auto
>>
>> The SELECT statement JOINS the WITH results on *original_category*.
>> Something like this maybe...
>> 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
>>
>>
Hi Robert,

I appreciate your help.  I believe you have the right idea, but being a
novice still, I do not really understand how to apply this approach.  I
tried following this example (which is what I think you may have been
thinking of):
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;

No, I don't quite see how to apply it to my specific situation.  I assumed
(for better or worse) that your suggested 3 columns are simply column names
to be used for the new (temporary, I assume?) table, but I get an error that
'original_category' does not exist.  I did try using my actual column names
from tblcategory, but that did not help any either.  Not that I thought it
would, but I didn't know what else to try.

If it helps, I can send you a spreadsheet with some sample data to give you
a better idea of the tables/data involved.  Also, don't hesitate to
criticize my category table design - I just borrowed from a web tutorial,
and thought I could apply it to my db.  If there is a better way to skin the
cat, I'm all ears.  That said, I think your suggestion is probably a good
one.  And I really should learn to use this type of query - might come in
handy in other situations.  :-)

I'm also curious as to the performance of this type of query - when I tried
the initial example with the integer, it seemed fairly rapid.  I also tried
it using the example below the one I pasted in here (from the docs link you
posted) - that produced a 270-row table that I did not think would be usable
for the rest of the query.  But it seems efficient enough for general
purposes.

Thanks again for taking a look at my problem.
Blessings,
Don
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://ofb.biz/pipermail/christiansource_ofb.biz/attachments/20110804/c6133240/attachment.htm>