I am using FireBird 1.5.3. The DB contains two tables:

CREATE TABLE FOLDERS (
ID INTEGER NOT NULL,
NAME VARCHAR(256) NOT NULL,
PARENTID INTEGER
);

CREATE TABLE FILES (
FOLDERID INTEGER NOT NULL,
FILENAME VARCHAR(256) NOT NULL
);


These are in one-to-many relationship (to mimic the file system
organisation):

ALTER TABLE FILES ADD CONSTRAINT FK_FILES FOREIGN KEY (FOLDERID)
REFERENCES FOLDERS (ID)

Example data:

Table FOLDERS
1 A NULL
2 B 1
3 C 2

It's like a folder tree. C is inside B, while B is inside A.

Table FILES
3 X
3 Y
2 Z

I.e. the files X and Y are linked to folder C. The file Z is linked to
the folder B. Additionally, I have a stored procedure, which returns
the full path for any given sub-folder. For example, GETPARENTS(3)
returns the full path A/B/C.

CREATE PROCEDURE GETPARENTS (
ID INTEGER)
RETURNS (
FULLPATH VARCHAR(2048))
AS
declare variable did integer;
declare variable oid integer;
declare variable name varchar(256);
BEGIN
FULLPATH='';
WHILE (:ID IS NOT NULL) DO
BEGIN
SELECT F.ID, F.PARENTID, F.NAME
FROM FOLDERS F
WHERE F.ID = :ID
INTO :DID, :OID, :NAME;
ID = :OID;
FULLPATH = :NAME || '\' || FULLPATH;
END
SUSPEND;
END

Now the problem: I need to select some files and their full paths,
preferably in one statement. That is, I need something like

select getparents(folders.id), filename from files inner join folders on
folders.id=files.folderid where filename = 'A'

Which obviously does not work. Please tell how can I do it correctly and
with the best efficiency?





[Non-text portions of this message have been removed]