Complex Query
 

Greeting

When it comes to SQL I'm a novice and I think what I want to do is WAY outside my expertise. I have a file management system called ProjectWise. Everything to create the folder path is located in a single table. You start with the lowest folder then you need to find its parent. Once you've found the that parent you need to finds that folders parent and so on until you've created the entire path. I've attched an image file that shows this much better. I have no idea how to build this path with everything being in a single table. I don't mind reading I just need to be pointed in the right direction. Thank you for any help.

Re: Complex Query
 

I was thinking you could do it by joining the table to itself. But then you have the problem of not knowing if the page you're looking up is in the 3rd or 4th level of 'childhood'.

Anyway, this is the article on joining tables and this is the article on joining a table to itself.

If you decide to work with joins, you'll end up with a query similar to this:

SELECT

 t1.filename AS file,

 t2.filename AS parent1,

 t3.filename AS parent2 FROM files AS t1

INNER JOIN

 files AS t2

 on t2.fileID = t1.parentID

INNER JOIN

 files AS t3

 on t3.fileID = t2.parentID

WHERE

 a1.fileID = 'the file you are looking for';

You'll get a table that looks like this:

FILE | PARENT1 | PARENT2
--------------------------------------------------------------------
corridor_data | bia_walden_point | alaska



*EDIT #2*
This query will work only if there are 2 parent folders... I don't know how to dynamically change the query depending on the level of the file... sorry.

Re: Complex Query
 

I've thought about that. The biggest problem with it is that some of the path could be 8 to 10 parents deep. There is no way of knowing. I thought about a loop and just loop until o_parentno = 0 but I can't figure out how to read the o_projectno and then populate o_parentno with the value that came from o_projectno and loop through it again. Let alone display the folder name for each o_parentno.

Re: Complex Query
 

It's really the most hard question I've met! loool I don't use to write SQL frequently, I just did the half of task to you, is to get parent ID, please care and ask if you didn't understand anything in my script, I work more than 2 hours in :(

Create proc GetParent

@Child int,

@Parent int output

AS

SELECT @Parent = o_parentno

FROM         phillip

WHERE     o_projectno = @Child



Create proc GetAllParent

@ChildID int

AS

        if @ChildID != 0

                begin

                        Declare @CurrentParent int

                        while @ChildID != 0

                                begin

                                        exec GetParent @ChildID, @Parent = @CurrentParent output

                                        Select @CurrentParent

                                        set @ChildID = @CurrentParent

                                        if @ChildID <= 0 

                                                break

                                        Else

                                                continue

                                end

                end

Re: Complex Query
 

There is something called 'case expressions' in MS SQL queries...

This article is a bit outdated but it still makes a lot of sense. Jump to the "A more complicated example" section to see how he does it.

Re: Complex Query
 

What shall you to do is to call GetAllParents and give it the ChildID you need to get all its parents

GetAllParent 27937

Re: Complex Query
 

WOW This is great... I can work with this. I've put the syntax into SQL Query Analyzer and ran it. It stops on Line 9 "CREATE PROC GetAllParent". But I can work with this. With the article you provided and a little more digging maybe I can figure this out. Thanks again for all your help.

Re: Complex Query
 

Welcome, Phillip please mark it as solved if it be.