Link to home
Start Free TrialLog in
Avatar of CalmSoul
CalmSoulFlag for United States of America

asked on

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'
Avatar of Sean Stuber
Sean Stuber

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.
Avatar of CalmSoul


SELECT p.fldstring,
  FROM document d
       INNER JOIN folder f ON = 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
Sorry one change FOLDID = {ABCD}56777 instead of ABCD-56777

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


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

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hello sdstuber:

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

It worked for me with the data you provided.

Can you post an example where it doesn't work?