Re: SQL Table Design

Thanks to all that replied. I think you have all solidified that the old
non-normalized database table should be a thing of the past :-) Just what I
wanted to here.

So here is the DDL for my table:
CREATE TABLE WEBVISION.ITEM_CATEGORIES (
CATEGORY_ID FOR CAT_ID INTEGER ,
CATEGORY_DESCRIPTION FOR CAT_DESC VARCHAR (128) ,
PARENT_CATEGORY_ID FOR P_CAT_ID INTEGER DEFAULT NULL ,
INCLUDE_CATEGORY FOR INC_CAT CHARACTER (1),
PRIMARY KEY (CATEGORY_ID),
CHECK(INCLUDE_CATEGORY IN('N', 'Y')) )
NOT VOLATILE RCDFMT ITEMCATR ;

Here is the SQL statement to find the children categories.

WITH temp_cat(category_id, parent_category_id, iteration) AS (
SELECT item_cat.category_id, item_cat.parent_category_id, 0
FROM mylib.item_categories AS item_cat
WHERE item_cat.category_id = ?
UNION ALL
SELECT item_cat.category_id, item_cat.parent_category_id, temp.iteration
+ 1
FROM webvision.item_categories AS item_cat
JOIN temp_cat AS temp ON temp.category_id = item_cat.parent_category_id)

SELECT *
FROM temp_cat

--
James R. Perkins
http://twitter.com/the_jamezp