[CS-FSLUG] SQL Query Question

[Don Parris]

Anyone up for an SQL query question?  It should be mostly RDBMS-agnostic,
but I'm using PostgreSQL 8.4/Ubuntu 11.04.

I have a table 'tblcategory' and a table 'tbltransdetails'.  The first holds
3 levels of categories, the second holds the details of transactions, and
stores the category_id from tblcategory for each transaction_item record.  I
have a nice query that shows all my expenses by lowest-level category, but
what I want is to show the expenses by the highest-level category.

tblcategory (category_id, category_name, parent_id, lineage, deep)
tbltransdetails (transdetails_id, transaction_id, category_id, item, size,
quantity, amount, note)

Category Example:
Transportation: Auto: Fuel (parent(7): child(55): grandchild(60) with a
lineage of 7-55-60 and depth of "2"

If I do:
SELECT catgory_name AS "Category", sum(transdetails_amount)
FROM tblcategory c, tbltransdetails d
WHERE c.category_id = d.category_id
GROUP BY category_name;

This gives me the detailed category view, something like: Transportation:
Auto: Fuel | 150.00
What I want is to build a query that shows the amounts per *parent*
category, not the grandchild category.  In other words, I only want to see
Transportation | 250.00

I can build a query that shows the parent & child categories for all 3
levels, or even for just the first 2 levels by performing a self-join on
tblcategory.  I got that down relatively ok.  But I cannot figure out how to
select only the top category of the tree and relate it to the summed amounts
from the other table.  Can anyone help?

Thanks,
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/20110801/a16ae0f8/attachment.htm>