From: Matthew Nuzum <cobalt@bearfruit.org>
To: pgsql-sql@postgresql.org
Subject: lost on self joins
Date: Wed, 15 Jan 2003 13:29:47 -0500
Message-ID: <002d01c2bcc4$17e487a0$6700a8c0@mattspc> (raw)
Sometimes recursion makes my head spin...
Imagine that I have a database that holds the structure of my
filesystem. There is a table called files that contains every piece of
info you would ever want to know about a file, including a unique ID
called fileid.
| files
========
x| fileid
| filename
| ...
Then, you have a table called folders which looks like:
| folders
==========
x| folderid
| parentid (relates to folders.folderid)
| foldername
Finaly, a table to allow a many to many join called files_folders
| files_folders
================
x| ffid
| folderid (fk to folders.folderid)
| fileid (fk to files.fileid)
Now, I'd like to create a view that shows everything in files, as well
as the complete path to the file. However because I don't know how many
levels deep the file is nested, I'm not sure how to get that complete
path. Here is conceptually what should come out:
| files_view
==============
x| fileid
| filename
| ...
| full_path
Something that won't work is:
SELECT files.*, folders.foldername, folders2.foldername
FROM files, folders, folders folders2, files_folders ff
WHERE files.fileid = ff.fileid
AND ff.folderid = folders.folderid
AND folders.parentid;
The problem is that files that are not in a folder won't show up, and if
a folder is more than two levels deep it will only show the two highest
levels.
Can anyone suggest a way for me to get the information I need? I'm very
content to use a simple pl/pgsql function, however I don't know how I'd
use recursion there.
Thanks,
--
Matthew Nuzum
www.bearfruit.org
cobalt@bearfruit.org
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cobalt@bearfruit.org
Subject: Re: lost on self joins
In-Reply-To: <002d01c2bcc4$17e487a0$6700a8c0@mattspc>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox