Hi I have a database which is basically documents and folders. I want to produce an SQL to show the location of a particular document/folder. What I have come up with so far is this: select d.name, f1.name, f2.name, f3.name, f4.name, f5.name from documents d left join folders f1 on d.id_parent=f1.id_object left join folders f2 on f1.id_parent=f2.id_object left join folders f3 on f2.id_parent=f3.id_object left join folders f4 on f3.id_parent=f4.id_object left join folders f5 on f4.id_parent=f5.id_object where d.id_object = '58964987' This gives me the info I need but is slow to run – is this the best way of doing this? Also this only gives 5 locations – it could be that the document/folder could have 15 – would I need to then do this up to folder 15 or is there a better way? I was also wishing to list all document in a particular folder and within any folders within that folder. I deleted where d.id_object = '58964987' and added this to the above: where (d.id_parent = 'fA19780' or f1.id_parent = 'fA19780' or f2.id_parent = 'fA19780' or f3.id_parent = 'fA19780') Again this gave me what I needed but was slow and if I went upto 15 layers of folder I would have to add and type this 15 times – is there a better way? Many thanks Paul Hi I have a database which is basically documents and folders. I want to produce an SQL to show the location of a particular document/folder. What I have come up with so far is this: select d.name, f1.name, f2.name, f3.name, f4.name, f5.name from documents d left join folders f1 on d.id_parent=f1.id_object left join folders f2 on f1.id_parent=f2.id_object left join folders f3 on f2.id_parent=f3.id_object left join folders f4 on f3.id_parent=f4.id_object left join folders f5 on f4.id_parent=f5.id_object where d.id_object = '58964987' This gives me the info I need but is slow to run – is this the best way of doing this? Also this only gives 5 locations – it could be that the document/folder could have 15 – would I need to then do this up to folder 15 or is there a better way? I was also wishing to list all document in a particular folder and within any folders within that folder. I deleted where d.id_object = '58964987' and added this to the above: where (d.id_parent = 'fA19780' or f1.id_parent = 'fA19780' or f2.id_parent = 'fA19780' or f3.id_parent = 'fA19780') Again this gave me what I needed but was slow and if I went upto 15 layers of folder I would have to add and type this 15 times – is there a better way? Many thanks Paul

The problem with the approach your taking is that you will always be limited to the pre-determined number of levels you built into the query If your report is only looking for the path of one folder at a time, I think you'd have better luck with an approach more like this one... DECLARE @Object INT, @Parent INT, @Path VarChar(MAX), @Object2 INT SET @Object = 2028 SELECT @Parent = id_parent FROM Documents WHERE id_object = @Object SELECT @Path = Name FROM Documents WHERE id_object = @Object SET @Object2 = @Object WHILE @Parent IS NOT NULL BEGIN SET @Object = @Parent SELECT @Parent = id_parent FROM Documents WHERE id_object = @Object SELECT @Path = Name + ' > ' + @Path FROM Documents WHERE id_object = @Object END SELECT @Object2 AS id_object, @Path AS Object_Path This will produce 2 columns id_object & Object_Path. The Object_Path will be formatted like this... Top Level Object > Second Level Object > Third Level Object > Fourth Level Object > The Object Being Queried The advantage here is that it doesn't matter how many levels there in the path, you'll never have too many or too few columns to accommodate the data. Jason Long

Hi Jason Im not in work for about a week now so created a test database at home with just 2 table like below: Folders: 1 Global Folder NULL 100 Test Folder1 1 634 Paul 100 671 Paul2 634 747 Hayley 100 749 Hayley2 747 101010 Test2 100 101013 Test3 100 150 HR 1 160 Staff 150 164 Current 160 173 Former 160 190 Security 150 Documents 111 Paul Doc 749 When using your SQL I get no results - executes for ever but no results are produced. Am i doing something stupid?!! Many thanks Paul

Paul, I have to head to lunch right now but I'll take a look as soon as I get back. Jason Long

I'm not sure where you're having problems. I just took it for a test drive and it worked just fine... -- Place the test data into a temp table -- IF OBJECT_ID('tempdb..#Documents') IS NOT NULL DROP TABLE #Documents CREATE TABLE #Documents (id_object INT, Name VarChar(25), id_parent INT) INSERT INTO #Documents SELECT 1 AS id_object, 'Global Folder' AS Name, NULL AS id_parent UNION ALL SELECT 100,'Test Folder1', 1 UNION ALL SELECT 634,'Paul', 100 UNION ALL SELECT 671,'Paul2', 634 UNION ALL SELECT 747,'Hayley', 100 UNION ALL SELECT 749,'Hayley2', 747 UNION ALL SELECT 101010,'Test2', 100 UNION ALL SELECT 101013,'Test3', 100 UNION ALL SELECT 150,'HR', 1 UNION ALL SELECT 160,'Staff', 150 UNION ALL SELECT 164,'Current', 160 UNION ALL SELECT 173,'Former', 160 UNION ALL SELECT 190,'Security', 150 -- The actual solution -- DECLARE @Object INT, @Parent INT, @Path VarChar(MAX), @Object2 INT SET @Object = 671 --(enter the id_object for Paul2) SELECT @Parent = id_parent FROM #Documents WHERE id_object = @Object SELECT @Path = Name FROM #Documents WHERE id_object = @Object SET @Object2 = @Object WHILE @Parent IS NOT NULL BEGIN SET @Object = @Parent SELECT @Parent = id_parent FROM #Documents WHERE id_object = @Object SELECT @Path = Name + ' > ' + @Path FROM #Documents WHERE id_object = @Object END -- And the final SELECT -- SELECT @Object2 AS id_object, @Path AS Object_Path And here is the result... id_object Object_Path 671 Global Folder > Test Folder1 > Paul > Paul2 Let me know if you have any more problems.Jason Long

Hi Jason sorry for the late reply-my laptop and a glass of water had a fight and my laptop came off worse! Using the temp table way work fine for me. the question i have is would this also work with 2 tables 'Documents' &'Folders'? I want to be able to search for a document ID in the Documents table and then list the folder location from the Folders table. just to clarify the document has its own id and then parent id which is the id of the first folder. many thanks Paul ps sorry for typing-im using the on-screen keyboard!

Paul, If you want to post some sample data, structured the way it is in your system, I can show you how to rewrite the query. Jason Long

Hi Jason Many thanks for your reply. If we take the example from before we have a table called 'Folders' which contains the following data: id_object Name id_parent 1 Global Folder NULL 100 Test Folder1 1 634 Paul 100 671 Paul2 634 747 Hayley 100 749 Hayley2 747 101010 Test2 100 101013 Test3 100 150 HR 1 160 Staff 150 164 Current 160 173 Former 160 190 Security 150 and a table called 'Documents' with data like: id_object name id_parent 111 Paul Doc 749 what i would like is to be able to type the doc id 111 and the location (hayley2,hayley,test folder1,global folder) to be given. Many thanks Paul

Well in that case you'd SET @Object = 749. The result would be... id_object Object_Path 749 Global Folder > Test Folder1 > Hayley > Hayley2 If you want the output formatted the wat you have it in your last post... (hayley2,hayley,test folder1,global folder) You just need to make a minor modification to line #30 SELECT @Path = @Path + ', ' + Name FROM #Documents WHERE id_object = @Object Now the results read like this... id_object Object_Path 749 Hayley2, Hayley, Test Folder1, Global Folder If you want to use the "111" id, you'll need to union the two tables. If you post the table structure of both tables I can show you how to do that too.Jason Long

Try it like this... DECLARE @Union TABLE ( id_object INT NOT NULL, name VarChar(50) NOT NULL, id_parent INT NULL) INSERT INTO @Union SELECT id_object, name, id_parent FROM Folders UNION ALL SELECT id_object, name, id_parent FROM Documents DECLARE @Object INT, @Parent INT, @Path VarChar(MAX), @Object2 INT SET @Object = 111 --(enter the id_object for PaulDoc) SELECT @Parent = id_parent FROM @Union WHERE id_object = @ObjectSELECT @Path = '' SET @Object2 = @Object WHILE @Parent IS NOT NULL BEGIN SET @Object = @Parent SELECT @Parent = id_parent FROM @Union WHERE id_object = @Object SELECT @Path = @Path + ', ' + Name FROM @Union WHERE id_object = @ObjectEND SELECT @Object2 AS id_object, RIGHT(@Path, LEN(@Path) -1) AS Object_Path Jason Long

Hi Jason Would it make difference if the document id was like 'A111' and folder id was like 'F247' Using the second one I get the error that #documents does not exist. If I delete the # and keep the documents it runs but again gives no results. If i use the temp table you setup it worked but I think that is because all the info is in the one table - 'documents' and not 'Documents' and 'Folders' So to be a pain! and sorry if im not explaining myself well. Thanks Paul

>> Using the second one I get the error that #documents does not exist. If I delete the # and keep the documents it runs but again gives no results. If i use the temp table you setup it worked but I think that is because all the info is in the one table - 'documents' and not 'Documents' and 'Folders' Look at it again. I've replace the #Documents with @Union. >> Would it make difference if the document id was like 'A111' and folder id was like 'F247'? Nope. Not as long as you make the necessary datatype adjustments. Look up the 2 tables in SSMS's Object Explorer and drill down to the Columns folder level. It will show the data types currently being used for those columns. Scan through the script from my last post and replace all of the instances where I've defined a data type as INT and change it. >> So to be a pain! and sorry if im not explaining myself well. No worries. Just bear in mind, for future reference, the more accurate you are upfront with the details... The easier it is for someone to give you a good solid answer and get it to you quicker. When you post your question as a hypothetical, the answer you can expect will also be hypothetical. Providing real table names, real column names and their actual data types makes the process far smoother. Obviously you can't provide real sample data for security reasons, but the made up sample data should at least be representative (in terms of structure) of the real data.Jason Long

Hi Jason Many thanks for your help. Looks like this is now working for me. Just one quick question more out if curiosity than anything else. Is creating the @Union table the same as creating a temp table? Next time I'll make sure ill be more accurate with my data up front. Point taken about being able to provide real columns and table names but not real data. Think I was just trying to be too cautious. Once again thanks for your help. Paul

>> Many thanks for your help. Looks like this is now working for me. Glad you got it working! >> Just one quick question more out if curiosity than anything else. Is creating the @Union table the same as creating a temp table? Very similar in several ways, but no. The @Union is a table variable. If you Google "sql server difference between temp table and table variable", you'll find plenty of information on both the differences and similarities. For the record... A temp table would have worked equally well in this particular situation. I just chose to use a table variable due to the simplified syntax. >> Next time I'll make sure ill be more accurate with my data up front. Point taken about being able to provide real columns and table names but not real data. Think I was just trying to be too cautious. Like I said, no worries. Just something to keep in mind. Sometimes it's tough to know which details are important for answering your question and erroring on the side of caution isn't a bad thing. >> Once again thanks for your help. You're welcome. :-) Jason Long