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 80 80

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:

 t1.filename AS file,
 t2.filename AS parent1,
 t3.filename AS parent2 FROM files AS t1
 files AS t2
 on t2.fileID = t1.parentID
 files AS t3
 on t3.fileID = t2.parentID
 a1.fileID = 'the file you are looking for';

You'll get a table that looks like this:

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 80 80

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 80 80

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
SELECT @Parent = o_parentno
FROM         phillip
WHERE     o_projectno = @Child

Create proc GetAllParent
@ChildID int
	if @ChildID != 0
			Declare @CurrentParent int
			while @ChildID != 0
					exec GetParent @ChildID, @Parent = @CurrentParent output
					Select @CurrentParent
					set @ChildID = @CurrentParent
					if @ChildID <= 0 
Re: Complex Query 80 80

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 80 80

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 80 80

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 80 80

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.