November 7, 2006 at 3:59 am
I have a table which stores hierarchy data
table name is Category
CT_Id Category ParentId
1 Hardware 0
2 Software 0
3 SQL 0
10 Floppy 1
11 RAM 1
12 Primary 11
13 Secondary 11
14 Office 2
The ParentCatId of first three values are 0 These are the topmost(main) category. These are not under any category.
I have another table TransactionMaster where I have column CT_Id and this column is a foreign key in this table and references
CT_Id column in category table
I am trying a write a query which will give me all the category names for which there has been a transaction. I can get this by a inner join with the category and TransactionMaster table.But apart from the category I also want to
retrieve the names of the parent category as a transaction can always be done on the lower most category only and never on a parent category.
example of output(if a transaction has been done on Primary category)
CT_Id Category TransactionAmount
1 Hardware
11 RAM
12 Primary 100
How can I get this output. Thanks for reading
November 7, 2006 at 4:32 am
This excellent article on hierarchies should help you.
John
November 7, 2006 at 12:12 pm
How about this one?
SELECT c.CT_Id CT_Id, c.Category Categor, COUNT(mt.CT_Id) FROM MasterTransaction mt, Category c GROUP BY mt.CT_Id HAVING mt.CT_Id = c.CT_Id
November 8, 2006 at 4:28 am
SELECT c.CT_Id , c.Category , COUNT(mt.CT_Id) FROM Category c left outer join MasterTransaction mt on mt.CT_Id = c.CT_Id GROUP BY mt.CT_Id
Since parent categories have no transactions, their count would be 0.
If you need distinction between parent categories that can't have transactions and terminal categories that have no transactions, you have to join it to recursive view and make a union of terminal and non-terminal categories. Alternatively, you can add a flag field "terminal" to categories table.
November 8, 2006 at 9:56 pm
Thanks for the replies. I am sorry if I couldn't explain earlier but the last column is the amount and not the count that I want.
Basically I just want to display all categories which have a transaction done on them, so I get the category name from the category table and the amount from the transactionmaster table by using a inner join on these 2 tables using the CT_id column.
But apart from only displaying the child categories only i also want to display its parent categories(which of course will not have any transactions against them in the transactionmaster table)
like the example which I posted in my earlier post
if there is only 1 record in the transactionmaster table for CT_id = 12 and amount = 100('Primary' category)
then with the inner join on the 2 tables i will get the result as
CT_Id Category Amount
12 Primary 100
but i actually want this(even its parent categories which are RAM and Hardware should be shown)
CT_Id Category Amount
1 Hardware
11 RAM
12 Primary 100
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply