Complex Query

[Phillip_Hess]

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.

[kanaku]

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.

[Phillip_Hess]

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.

[Ramy Mahrous]

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

[kanaku]

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.

[Ramy Mahrous]

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

GetAllParent 27937

[Phillip_Hess]

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.

[Ramy Mahrous]

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