CODEWITHSUNDEEP
sqlt-sqlsql-server-2005
hoppiness
hoppiness

Reputation: 43

TSQL -- Retrieve parent ID

The raw data in my table consists of an ID column and a column 'IsFolder' which can be 1 or 0, like this: 

ID    IsFolder
1     1
2     0
3     0
4     1
5     0
6     0
7     0
8     0
9     0
10    0
11    1
12    0
13    0
14    0
15    1
16    0
17    0

The way the code interprets this is that if a line has a 1 for 'IsFolder' then it is a Folder, and all of the tasks below it (until you hit the next Folder) are children of that folder.

What I'd like to have is a Select statement that will just return the ID of the parent folder for all non-folder tasks. So something like:

ID    ParentFolder
2     1
3     1
5     4
6     4
7     4
8     4
9     4
10    4
12    11
13    11
14    11
16    15
17    15

I'm using MS SQL Server Management Studio 2005. I feel like this is an easy answer to those familiar with using cursors (which I am not). I can't think of any other way to do it but maybe someone else can. Anyway thanks in advance for any answers and sorry if I did something wrong, this is my first post.

Upvotes: 0

Views: 97

Answers (2)

gfreeman
gfreeman

Reputation: 143

I'd just like to point out that this task would be much easier, and the overall design much more extensible, if the structure of the data was changed a little. How are you going to add a task to folder 4, for example? If the parent and child relationship was extracted to two different tables, it might help.

Upvotes: 1

D Stanley
D Stanley

Reputation: 152566

You don't need a cursor for that - just a subquery:

SELECT ID, 
       (SELECT MAX(ID) 
        FROM Folders 
        WHERE ID < f.ID
           AND IsFolder = 1) AS Parent
FROM Folders f
WHERE IsFolder = 0

Upvotes: 3

Related Questions