Parent/Child Relationships in tables - not quite working...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Parent/Child Relationships in tables - not quite working...

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-08-20 : 07:53:03

Phillip B Oldham writes "Hi.

I'm having problems with a single table which is used to output a list of folders in a tree format.

I have a table similar to the following:


-------------------------
+ id + folder  + parent +
-------------------------
+  0 + Root    +      0 +
+  1 + Folder1 +      0 +
+  2 + Folder2 +      0 +
+  3 + Sub1    +      2 +
+  4 + Folder3 +      0 +
+  5 + Sub2    +      2 +
-------------------------

What I'm looking to get is the following:


Table:
------------------------
+  parent  +   child   +
------------------------
+     Root +      Root +
+     Root +   Folder1 +
+     Root +   Folder2 +
+  Folder2 +      Sub1 +
+  Folder2 +      Sub2 +
+     Root +   Folder3 +
------------------------

Structure:
+Root
  +Folder1
  +Folder2
    +Sub1
    +Sub2
  +Folder3

I'm using the following SQL Statement, which is working in so far as its pulling the information out of one table:


SELECT p.folder AS parent, c.folder AS child
FROM table AS p, table AS c
WHERE p.id = c.parent;

But what I'm getting is the following:


Table:
------------------------
+  parent  +   child   +
------------------------
+     Root +      Root +
+     Root +   Folder1 +
+     Root +   Folder2 +
+  Folder2 +      Sub1 +
+     Root +   Folder3 +
+  Folder2 +      Sub2 +
------------------------

Structure:
+Root
  +Folder1
  +Folder2
    +Sub1
  +Folder3
    +Sub2

I've tried to order by id, but it throws the ordering out completely, with Folder2 (+subfolders) coming after Folder3.

Can you help me please?

Thanks!"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-20 : 12:32:54

Could you post the DDL (CREATE TABLE statements) for your table as well as some INSERT INTO statements for sample data? With all of the plus signs and dashes in the data, I can't tell what's going on. Also, why are you putting this kind of data in a table? Wouldn't it be easier to look at the file system for this?

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources