Requires Free Membership to View
Thanks for making part of this problem easy. Ordinarily, one would not expect to find "associated elements" in the category table. For example, product sales would be in a sales table, which would be joined to a product master table, which would in turn be joined to the category table, and then there would be a GROUP BY on the category to get SUM(productsales). However, if these joins are "pre-defined" into a view, then we can simply use the view in the same way that we're going to use the sample category table defined below, which has the sales data incorporated as one of the "associated elements". It makes the query that much easier.
If you're comfortable with a self-referencing hierarchical table, you can skip right to Part 3 of this answer. The remainder of Parts 1 and 2 will be devoted to building up the category hierarchy, for those who are new to the structure.
We'll start by populating the category table with the "top level" or major categories:
create
table categories
( id smallint identity not null
, parentid smallint null
, descr varchar(15) not null
, salesamt integer null
, constraint categories_pk
primary key ( id )
, constraint parent_category_fk
foreign key ( parentid )
references categories ( id )
)
insert
into categories ( descr )
values ('Category A')
insert
into categories ( descr )
values ('Category B')
insert
into categories ( descr )
values ('Category C')
insert
into categories ( descr )
values ('Category D')
Now let's run a SELECT to see what we've got so far:
select * from categories
id parentid descr salesamt ------ -------- --------------- ----------- 1 NULL Category A NULL 2 NULL Category B NULL 3 NULL Category C NULL 4 NULL Category D NULL
Notice that the parentid is null; this is what identifies categories at the top level. (They don't have sales amounts, either; they could, but that would make the query messier.)
Now let's add a subcategory:
insert
into categories ( descr, parentid, salesamt )
values ( 'subcat A1', 1, 103 )
Notice that the parentid value for this subcategory is given as 1, which corresponds to Category A. In other words, we need to know the value of the parent category's id before we can insert a subcategory under that category.
Now let's assume we've added several additional subcategories in this manner, and run another SELECT to see the categories and their subcategories:
id parentid descr salesamt ------ -------- --------------- ----------- 1 NULL Category A NULL 2 NULL Category B NULL 3 NULL Category C NULL 4 NULL Category D NULL 5 1 subcat A1 103 6 1 subcat A2 207 7 1 subcat A3 94 8 1 subcat A4 255 9 1 subcat A5 159 10 4 subcat D1 150 11 4 subcat D2 195 12 2 subcat B5 315 13 2 subcat B4 301 14 2 subcat B3 377 15 2 subcat B2 315 16 2 subcat B1 315
Continued in
Part 2.
This was first published in October 2003
Join the conversationComment
Share
Comments
Results
Contribute to the conversation