SELECT from a table that refers to itself?

Question

SELECT from a table that refers to itself?

tjustin7

SSC Journeyman

Points: 92
More actions
October 1, 2010 at 4:21 pm

#387926

Greetings, perhaps it's just Friday and my brain is fried, but
how would I go about selecting information from a table that references itself?
Example:
Table FOLDERS:
ID NAME PARENTID
--------------------------------------
0 Root NULL
34 System32 33
33 Windows 0
32 Drivers 34
31 System 33
Table FILES:
PARENTID NAME FILEID
--------------------------------------
34 kernel32.dll 99
33 explorer.exe 100
How would you find the FULL path of an item from the FILES table?
i.e. Desired result:
[kernel32.dll][[Root][Windows][System32]
Obviously one level deep is just
Select * from Files, Folders
WHERE files.parentid = folders.id and
FILEID = 99
...
but how do you follow ALL the "links" back to their highest level?
Is that something that would have to be done outside of SQL?
Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Nope, can do it in 2k5 and 2k8 with a recursive CTE definition.

If you'd like a sample of how to build it, check out the first link in my sig and setup some DDL/Test data for us to return to you a query that can help you out.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

R.P.Rozema SSChampion Points: 12317 More actions · Answer 2

The best way to do this is using common table expressions (cte for short). cte's can be made recursive, so they are ideal for your purpose.

use tempdb

go

create table dbo.folders (

FolderID int not null,

Name varchar(256) not null,

FolderIDParent int null,

constraint FK_folders_parent foreign key (FolderIDParent) references dbo.Folders(FolderID),

constraint PK_folders primary key(FolderID)

);

create table dbo.files (

FileID int not null,

FolderID int not null,

name varchar(256) not null,

constraint FK_files_folder foreign key (FolderID) references dbo.Folders(FolderID),

constraint PK_files primary key(FileID)

);

insert dbo.Folders(FolderID, Name, FolderIDParent)

select 0, 'Root', NULL

union all select 34, 'System32', 33

union all select 33, 'Windows', 0

union all select 32, 'Drivers', 34

union all select 31, 'System', 33

insert dbo.Files(FolderID, Name, FileID)

select 34, 'kernel32.dll', 99

union all select 33, 'explorer.exe', 100

with ctePath as (

select convert(varchar(max),quotename(f.Name)) + quotename(p.name) as Path, p.FolderIDParent

from dbo.Files f

inner join dbo.Folders p on (p.folderID = f.folderID)

union all

select c.Path + quotename(p.Name), p.FolderIDParent

from ctePath c

inner join dbo.Folders p on (c.FolderIDParent = p.FolderID)

)

select Path

from ctePath

where FolderIDParent is null

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

tjustin7 SSC Journeyman Points: 92 More actions · Answer 3

Awesome!

This probably saved me quite a bit of time banging my head on the desk!

🙂

Thanks!