Databases
StackOverflow2013
Postsdbo
PONeed optimized query or stored procedure for document management system
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PONeed optimized query or stored procedure for document management system
    text
    copied!<p>I am working on some document management system and need to provide document sharing feature to users. I have two tables one contains information of uploaded document(say Table 1) and other table contains information of document sharing with users(say Table 2).</p> <p>Please see image below: <img src="https://i.stack.imgur.com/snLDq.png" alt="enter image description here"></p> <p>Now I need to display listing of document as per sharing done with users. W.r.t to above tables, as 'Folder 3' in Table 1 is child of 'Folder 2' which is child of 'Folder 1'.</p> <p>Now only 'Folder 3' is shared with user '1234' (see in Table 2), when document list will be shown as first of all only parents will be shown by default, in our case 'Folder 1'.</p> <p>In my requirement I need to show only parents to be shown for navigation of child folder, as Folder one has one more child 'Document 1' and Folder 2 has one more child 'Document 2'.</p> <p>But when i will open with User '1234' what I need to show is:</p> <ol> <li>First page 'Folder 1'.</li> <li>Clicking on 'Folder 1' shows only 'Folder 2' in it, not 'Document 1' as only 'Folder 3' is shared with user which comes under 'Folder 2'.</li> <li>Now clicking on 'Folder 2' shows only 'Folder 3' in it, not 'Document 2' as only 'Folder 3' is shared with user.</li> <li>Now Finally when we click on 'Folder 3' we will show all document and folder comes under 'Folder 3' as parent is shared with user.</li> </ol> <p>I hope I am able to explain my problem. I need your help to prepare some query or a stored procedure to get required data efficiently. I am using MSSQL Server 2008 as database.</p> <p>Thank You in advance.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload