Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

folder path complex loop query

Posted on 2014-03-11
13
Medium Priority
?
310 Views
Last Modified: 2014-03-13
- 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
0
Comment
Question by:CalmSoul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39921267
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
 
LVL 5

Author Comment

by:CalmSoul
ID: 39921315
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39921430
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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 5

Author Comment

by:CalmSoul
ID: 39921554
Sorry one change FOLDID = {ABCD}56777 instead of ABCD-56777
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39921628
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
 
LVL 5

Author Comment

by:CalmSoul
ID: 39922280
What happens for instance there is no "FOLDID" for DOC_NAME?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39922303
If there's no foldid then the join conditions won't pick up those records.
0
 
LVL 5

Author Comment

by:CalmSoul
ID: 39922327
Can we pick up those records?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39922429
post sample data and expected results for that condition
0
 
LVL 5

Author Comment

by:CalmSoul
ID: 39922440
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39922466
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
 
LVL 5

Author Comment

by:CalmSoul
ID: 39925005
Hello sdstuber:

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

thanks
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39925012
It worked for me with the data you provided.

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Via a live example, show how to take different types of Oracle backups using RMAN.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

671 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question