hierarchical query 2005

SQL Apprentice Question
I am working with visual studio 2005 and sql server 2005 workgroup edition.
I have three tables where each row has an ID and a PID.
What I want to do is create either a hierarchical query to fill a data set
or create the dataset itself as an hierarchial dataset.
Is this possible with the tools I`m using and if so, how?


Celko Answers
>> I have three tables where each row has an ID and a PID. <<


Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

Also, why did you think that "id" and "pid" have any meaning to other
people? "id" is a postfix in ISO-11179 which is too vague to stand by
itself -- it begs the question "identiifer of what?" by its very
nature.



>> What I want to do is create either a hierarchical query to fill a data set or create the dataset itself as an hierarchial dataset. <<


What is a " hierarchical query"? That term was never used in SQL while
I was on the Committee. You might want to get a copy of TREES &
HIERARCHIES IN SQL for *several* different ways to model a hierarchy or
a tree. But first, you might want to learn some basics.


SQL Apprentice Question
I`m sorry for being a bit unclear.
the tables are:

category:
cat_id int (PK)
cat_name nvarchar


subcategory:
subcat_id int (PK)
parent_id int
subcat_name nvarchar


items:
item_id int (PK)
parent_id int (can be category or sub-category)
item_name nvarchar
item_desc nvarchar


I want to display the data (in some control, tree or other, I`m open to
suggestions)
so that each category contains the items and sub-categories where the parent
id is the id of the category and so on going into the hierarchy.


Celko Answers

>> I`m sorry for being a bit unclear. <<>> );


Google "nested sets model" explain the following


CREATE TABLE FishTaxonomy
(fish_id INTEGER NOT NULL
REFERENCES Fishes (fish_id)
ON UPDATE CASCADE,
lft INTEGER NOT NULL UNIQUE,
rgt INTEGER NOT NULL UNIQUE,
CHECK(lft < rgt) );


A given fish_id and all their superiorss, no matter how deep the tree.


SELECT F2.*
FROM FishTaxonomy AS F1, FishTaxonomy AS F2
WHERE F1.lft BETWEEN F2.lft AND F2.rgt
AND F1.fish_id = :my_fish_id;


2. The fish_id and all their subordinates. There is a nice symmetry
here.


SELECT F1.*
FROM FishTaxonomy AS F1, FishTaxonomy AS F2
WHERE F1.lft BETWEEN F2.lft AND F2.rgt
AND F2.fish_id = :my_fish_id;