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?

Thanks in advance.
Elad.

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 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.

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.

Show quoteHide quote

Look up "common table expressions" in SQL Server 2005 Books Online. There are
plenty of examples there.

Fish taste mush better if you catch them yourself. ;)


ML

---
http://milambda.blogspot.com/

Mabye... if you want sushi:(
The code that most people wind up with doing this without dedicated
constructs is also sushi - great if you like to eat.... bait :)

best,
http://racster.blogspot.com/

So, I guess you prefer the good old mix of up to 2000 different cows in a bun
with a lettuce leaf as decoration?


ML

---
http://milambda.blogspot.com/

I think you have misinterpreted my metaphor.
You have my apology if you found it offensive.

best,
steve

Show quoteHide quote

Nah. :) Never offensive, but confusing your posts are.

CTEs are bait? What are you trying to catch?


ML

---
http://milambda.blogspot.com/

You are still vague and still have not posted an DDL.  Category as a
table is absurd -- do you really have only one?  What does this entiy
or relationship dealo with?  Fish?  Trees? automobiles?

In a hierarchy, an element can be both a category and subcategory.
Your non-DDL does not have any relationship between "Category" and
"subcategory" tables.  Then there seems to be the design flaw of
putting both structure and values into the same table.

Let me assume that you are dealing with fish, since Steve posted a
crack about sushi.

CREATE TABLE Fishes
(fish_id INTEGER NOT NULL
fish_name VARCHAR(20) NOT NULL,
  <<other stuff>> );

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;

--CELKO-- skrev:

Heh, was this intentional? My preciousss ;)

    /impslayer, aka Birger Johanssssssson

If I am understanding correctly...

You will have one parent category, which will have one or more subcategories
directly beneath it, and the subcatagories can also have subcategories?

Then you have items which will never have children, but can be contained
within a category or a sub category?

The first thing you need to do is read up a little on hierarchies so you can
choose the best structure for your data.  There are a number of approaches
you can use, but you need to choose which one.  Here is one that you can
look at:

http://milambda.blogspot.com/2005/07/climbing-trees-is-for-monkeys.html

In your model, you have at least one table too many.  Category and sub
category should be in the same table.  You can identify the top level
categories in different ways.  Here are a few...
1. Add a column that indicates category type
2. have the categories be their own parents
3. Indicate categories by not having a parent

If items never have children, then you can get away with having them in
their own table.

Show quoteHide quotedata set or create the dataset itself as an hierarchial dataset. <<
Show quoteHide quote

Hi Elad,
My understanding of your issue is:
You have a table with parent-child relationship.You just want to know how
to compose a query for filling a dataset.
If I have misunderstood, please let me know.

Here is a example which may give you some hints:
CREATE TABLE PCTEST
(
ID int  primary key,
PID int not null,
SNAME char(10) not null
)

INSERT INTO PCTEST VALUES(1,0,'AA1')      
INSERT INTO PCTEST VALUES(2,0,'AA2')      
INSERT INTO PCTEST VALUES(3,1,'AA11')
INSERT INTO PCTEST VALUES(4,1,'AA12')     
INSERT INTO PCTEST VALUES(5,2,'AA21')     
INSERT INTO PCTEST VALUES(6,2,'AA22')     
INSERT INTO PCTEST VALUES(7,1,'AA13')

--Here is a query for retrieving its parent records according to parent id.
DECLARE @pid int
SET @pid=1
SELECT t1.* from PCTEST t1 INNER JOIN PCTEST t2 ON t2.PID=t1.ID WHERE
t2.PID=@pid

You may also tell me your detailed requirements so that I can further
assist you.

If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Hi,

I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.

Have a great day!

Charles Wang
Microsoft Online Community Support

I have actualy decided to create the hierarchy in my C# code as I add items
to the tree...since SQL does not have something like Oracles "Connect By"
such a query in SQL is just not fast enough for me.

Show quoteHide quote

Thanks for your updating and response.

Unfortunately SQL Server doesn't provide similar function like "Connect by"
in Oracle. It requires developer to write the SQL by himself. If you are
interesting, I recommend that you can give Microsoft feedback on this issue
and the SQL Team may include this function in next release.
The feedback link is:
http://connect.microsoft.com/

Anyway, I'm glad to see that you have resolved this issue via another way.
If you have any other questions or concerns, please feel free to let me
know.

Have a great day!

Charles Wang
Microsoft Online Community Support

Actually SQL Server 2005 include the CTE function which is the ansi standard
way to handle hierarchical queries.

Show quoteHide quote

Are you implying that the ansi standard is a 'gold' standard?
I would say it rates 'bronze' at best:)
That users still have to fumble around with base sql code for
hierarchies is ...well silly. Find a way to abstract the whole thing will
ya:)

Best,
steve
http://racster.blogspot.com

eladla,

You might want to read "Port CONNECT BY to DB2" by Serge Rielau.

I`ll check it out.
Thanks!

Show quoteHide quote

I like Serge. But reliance on the standard for DB2 is weak. They've had
what, 7/8 years, to do something sensible with hierearchies/recursion
and haven't done a thing. It is too convenient for vendors to hide behind
the standard. At least Oracle has made an attempt at abstraction.

Best,
steve
http://racster.blogspot.com

I totaly agree!
And what bugs me the most is that the hierarchy is built in to so many
controls for so many years (since the windows 3.11 I think)...it`s strange
there is no DB support for this feature.

Show quoteHide quote

Yeah! As a technique support, I also like to see this useful function could
be included into SQL Server. I think some difficulties occur and block
developers to dig deeply under current database model. Microsoft as well as
other companies are moving forward to this direction. Hope we can get this
useful function in a not long time.

Sincerely,
Charles Wang
Microsoft Online Community Support

This DOES exist in 2005.  You use CTEs for this.  It actually is not that
far from Oracle's implementation, so is fairly easy to use if you are
already familiar with the Oracle Connect by syntax.

Unfortunately, those of us on SQL 2000 are out of luck.

Show quoteHide quote

Just about everyone around here wants sql as a programmers
playground. I would like to see it as a different kind of playground.
Either way we will both lose.

http://racster.blogspot.com

well, looking at market shares and profits one might conclude that
following ANSI SQL standard too closely is probably bad for the
profits. I know I'm opening a can of worms...

Well, great! I appologize for my former replies.
CTE is indeed very helpful. Thanks for your kinda help on this issue.

Sincerely,
Charles Wang
Microsoft Online Community Support

Thanks Elad,
Your suggestions are valuable. I think Microsoft SQL Team is contributing
to improve SQL Server 2005.
For now, we can use CTE expression to resolve such hierarchy issue, though
it is not as concise as "connect by".

I write an example and hope it is helpful to you:
1. Assume I have a table called Imagination:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[Imagination]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Imagination](
    [ID] [int] NOT NULL,
    [NAME] [varchar](50) NOT NULL,
    [PID] [int] NOT NULL,
    [DESC] [varchar](50) NULL
) ON [PRIMARY]
END

INSERT INTO [Imagination] ([ID],[NAME],[PID],[DESC])VALUES(1,'P1',0,'1-0')
INSERT INTO [Imagination] ([ID],[NAME],[PID],[DESC])VALUES(2,'P2',0,'2-0')
INSERT INTO [Imagination] ([ID],[NAME],[PID],[DESC])VALUES(3,'P3',1,'3-1')
INSERT INTO [Imagination] ([ID],[NAME],[PID],[DESC])VALUES(4,'P4',2,'4-2')
INSERT INTO [Imagination] ([ID],[NAME],[PID],[DESC])VALUES(5,'P5',1,'5-1')
INSERT INTO [Imagination] ([ID],[NAME],[PID],[DESC])VALUES(6,'P6',1,'6-1')
INSERT INTO [Imagination] ([ID],[NAME],[PID],[DESC])VALUES(7,'P7',5,'7-5')
INSERT INTO [Imagination] ([ID],[NAME],[PID],[DESC])VALUES(8,'P8',2,'8-2')
INSERT INTO [Imagination] ([ID],[NAME],[PID],[DESC])VALUES(9,'P9',2,'9-2')
INSERT INTO [Imagination]
([ID],[NAME],[PID],[DESC])VALUES(10,'P10',2,'10-2')
INSERT INTO [Imagination]
([ID],[NAME],[PID],[DESC])VALUES(11,'P11',7,'11-7')
INSERT INTO [Imagination]
([ID],[NAME],[PID],[DESC])VALUES(12,'P12',5,'12-5')
INSERT INTO [Imagination]
([ID],[NAME],[PID],[DESC])VALUES(13,'P13',8,'13-8')
INSERT INTO [Imagination]
([ID],[NAME],[PID],[DESC])VALUES(14,'P14',20,'14-20')


2. Then I can use CET to query:   
WITH ImaginationReport ([ID],[NAME],[PID],[DESC],LEVEL)
AS
(
(SELECT *,0 AS [LEVEL] FROM Imagination WHERE PID=0)
UNION ALL
(
SELECT t1.[ID],T1.[NAME],t1.[PID],t1.[DESC],t2.LEVEL+1 AS [LEVEL] FROM
Imagination t1 INNER JOIN ImaginationReport t2
ON t1.PID = t2.ID
)
)

SELECT * FROM ImaginationReport order by LEVEL,PID

Then I can get the result like:
ID    NAME    PID    DESC    LEVEL
1    P1    0    1-0    0
2    P2    0    2-0    0
3    P3    1    3-1    1
5    P5    1    5-1    1
6    P6    1    6-1    1
4    P4    2    4-2    1
8    P8    2    8-2    1
9    P9    2    9-2    1
10    P10    2    10-2    1
7    P7    5    7-5    2
12    P12    5    12-5    2
13    P13    8    13-8    2
11    P11    7    11-7    3

Sincerely hope this can help you resolve your issue. The syntax seems
complex, however it should be better and more efficient than programming in
ASP.

If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.


Sincerely,
Charles Wang
Microsoft Online Community Support

problem with select
Force Leading Zeroes
Passing parameter into SP for permissions?
How to find Columns
sp_OAMethod error
SQL Server: query multiple databases
help to speed query
View limitations?
Missing Stored Procedures
max size of the record in each field