October 28, 2004 at 4:24 am
I have a table with dataid int, parentid int and dirname varchar(248). Basically it is a list of directories with an associated parent directory. I have been asked to write a query that lists the top directory first, then each of the top directory's direct child directories with each of the child directory's child directories under them. Below is sample table information and what the result should look like. The number of child directories may increase. We currently have about 5 levels.
I would appreciate any help/direction you gurus can give me! I am at a complete loss.
Thanks,
Bill
Sample Table Data
dataid, parentid,dirname
4000, 1000, Contract
5436, 4000, Task1
5695, 4000, Task2
5621, 4000, Task3
5840, 5436, MIS Weekly Report
5928, 5436, CMS Progress Report
6403, 5695, MISS Monthly Report
The result set should look like this:
Contract
Task1
MIS Weekly Report
CMS Progress Report
Task2
MISS Monthly Report
Task3
October 28, 2004 at 5:55 am
October 28, 2004 at 7:50 am
Thanks Kathikeyan, but that is just test data (I should have been more specific about that). The actual names of the directories are not in any type of sortable order). In fact, I will change the test data in my original post.
Thanks,
Bill
October 28, 2004 at 8:50 am
Bill,
Bit long winded and untidy, with a bit more time it could probably be cut down but:-
DECLARE @DataID int,
@MaxDataID int,
@ChildID int,
@MaxChildID int,
@ChildID2 int,
@MaxChildID2 int,
@ChildID3 int,
@MaxChildID3 int,
@ChildID4 int,
@MaxChildID4 int,
@ChildID5 int,
@MaxChildID5 int
SET @DataID = (SELECT MIN(DataID) FROM Directories)
SET @MaxDataID = (SELECT MAX(DataID) FROM Directories)
--Create a temp table to throw the data in
CREATE TABLE #MyTempTable (DataID INT, ParentID INT, Data varchar(50))
WHILE @DataID <= @MaxDataID
BEGIN
--Only Want Top level or Orphan / Single entries here
--First time in, insert parent table
INSERT INTO #MyTempTable (DataID, ParentID, Data)
SELECT DataID, ParentID, Data FROM Directories WHERE DataID = @DataID
--Check if the Parent has any Children
-----Check for Child 1st Level
IF (SELECT COUNT(DataID) FROM Directories WHERE ParentID = @DataID) > 0
BEGIN
--Get a Min and a Max child ID so we know when to stop
SET @ChildID = (SELECT MIN(DataID) FROM Directories WHERE ParentID = @DataID)
SET @MaxChildID = (SELECT MAX(DataID) FROM Directories WHERE ParentID = @DataID)
--For each child entry
WHILE @ChildID <= @MaxChildId
BEGIN
--Put the children into the table in order under the parents (Unless they have children of there own)
INSERT INTO #MyTempTable (DataID, ParentID, Data)
SELECT DataID, ParentID, Data FROM Directories WHERE ParentID = @DataID AND DataID = @ChildID
-----------Start Check for Child 2nd Level
IF (SELECT COUNT(DataID) FROM Directories WHERE ParentID = @ChildID) > 0
BEGIN
--Get a Min and a Max 2nd child ID so we know when to stop
SET @ChildID2 = (SELECT MIN(DataID) FROM Directories WHERE ParentID = @ChildID)
SET @MaxChildID2 = (SELECT MAX(DataID) FROM Directories WHERE ParentID = @ChildID)
--For each childs, child entry
WHILE @ChildID2 <= @MaxChildId2
BEGIN
--Put the childrens, children into the table in order under the parents (Unless they have children of there own)
INSERT INTO #MyTempTable (DataID, ParentID, Data)
SELECT DataID, ParentID, Data FROM Directories WHERE ParentID = @ChildID AND DataID = @ChildID2
---------------------------------------------------------
--Just keep repeating the code for child levels
---------------------------------------------------------
SET @ChildID2 = (SELECT MIN(DataID) FROM Directories WHERE ParentID = @ChildID AND DataID > @ChildID2)
END
END
-----------End Check for Child 2nd Level
SET @ChildID = (SELECT MIN(DataID) FROM Directories WHERE ParentID = @DataID AND DataID > @ChildID )
END
END
-----Check for Child 1st Level
SET @DataID = (SELECT MIN(DataID) FROM Directories WHERE DataID > @DataID)
--If Its already in the temp table we dont want to look at it again
IF @DataID IN (SELECT DataID FROM #MyTempTable)
BEGIN
WHILE @DataID IN (SELECT DataID FROM #MyTempTable)
BEGIN
SET @DataID = (SELECT MIN(DataID) FROM Directories WHERE DataID > @DataID)
END
END
END
--Get the results from the temp table, all sorted nice and pretty.....
SELECT * FROM #MyTempTable
--Clean up afterwards
DROP TABLE #MyTempTable
Run it in query analyser
Best regards
Steve
Steve
We need men who can dream of things that never were.
October 28, 2004 at 9:00 am
Sorry - do a find and replace with your table and column names.
I used columns: DataID, ParentID, Data
Table: Directories
Have fun
Steve
We need men who can dream of things that never were.
November 3, 2004 at 4:08 am
Thanks for the info Steve, I'll give it a try! I never got an email notification about the post, and I've been so busy recently I haven't had time to check! Thank you!
November 3, 2004 at 12:01 pm
Thanks Joe!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply