folder path complex loop query

- This is complicated query, we have 3 tables document, folder, path
- Table 1 (i.e. document) can be easily joined by table 2 based on ID and LID and we can get FOLDID
- We take that FOLDID and use that to construct the path where that document is located.
- TABLE 3 (Path) Do a split or substring on FOLDID and get the FLDKEY in above example 56777...Based on FLDKEY save the FLDSTRING as the "current folder" document is located. Loop through using FLDKEY2 in path table and find the second current folder again ... loop through again until FLDKEY2 = 'ROOT'
Build-Folder-Path---Sheet1.csv
LVL 5
CalmSoulAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
your sample data had the ABCD-56777 format instead of {ABCD}56777

so, if you'll always have at least document and folder then you'll INNER join those and OUTER join the path


SELECT p.fldstring folder_level_1,
       p2.fldstring folder_level_2,
       p3.fldstring folder_level3,
       d.doc_name
  FROM document d
       INNER JOIN folder f ON d.id = f.lid
       LEFT JOIN PATH p ON f.foldid = p.fldtype || '-' || p.fldkey
       LEFT JOIN PATH p2 ON p2.fldkey = p.fldkey2
       LEFT JOIN PATH p3 ON p3.fldkey = p2.fldkey2;


If you want the {} format then change

LEFT JOIN PATH p ON f.foldid = p.fldtype || '-' || p.fldkey

to

LEFT JOIN PATH p ON f.foldid = '{' || p.fldtype || '}' || p.fldkey
0
 
sdstuberCommented:
please post expected output that  corresponds to your sample input.

also your sample input seems incomplete given that we are supposed to look for "ROOT" elements but no such element exists in the sample.
0
 
CalmSoulAuthor Commented:
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
sdstuberCommented:
SELECT p.fldstring,
       p2.fldstring,
       p3.fldstring,
       d.doc_name
  FROM document d
       INNER JOIN folder f ON d.id = f.lid
       INNER JOIN PATH p ON f.foldid = p.fldtype || '-' || p.fldkey
       LEFT JOIN PATH p2 ON p2.fldkey = p.fldkey2
       LEFT JOIN PATH p3 ON p3.fldkey = p2.fldkey2;



Searching for ROOT explicitly doesn't seem to be necessary, once you reach the root, the chain will stop anyway because there won't be anything to connect fldkey and fldkey2
0
 
CalmSoulAuthor Commented:
Sorry one change FOLDID = {ABCD}56777 instead of ABCD-56777
0
 
sdstuberCommented:
change

       INNER JOIN PATH p ON f.foldid = p.fldtype || '-' || p.fldkey

to

       INNER JOIN PATH p ON f.foldid = '{' || p.fldtype || '}' || p.fldkey
0
 
CalmSoulAuthor Commented:
What happens for instance there is no "FOLDID" for DOC_NAME?
0
 
sdstuberCommented:
If there's no foldid then the join conditions won't pick up those records.
0
 
CalmSoulAuthor Commented:
Can we pick up those records?
0
 
sdstuberCommented:
post sample data and expected results for that condition
0
 
CalmSoulAuthor Commented:
0
 
CalmSoulAuthor Commented:
Hello sdstuber:

Left join is not doing the trick... Anything else I can try?

thanks
0
 
sdstuberCommented:
It worked for me with the data you provided.

Can you post an example where it doesn't work?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.