query which run in a nested form

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

query which run in a nested form

Author

Topic

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-12-19 : 05:16:27

hi

i need some help in a small problem but big one to me..

i have 2 table OIT_Permissions and OIT_Folder

OIT_Permissions :-
User_ID Folder_ID Folder_Level
----------- ----------- ------------
1 11 1
1 9 2
1 1 3
1 14 3
1 698 2
1 558 2
1 585 3
1 699 3

OIT_Folder
rec_id folder_name folder_parent
----------- ------------- ------------------
1 Recycle Bins 9
9 Administration 11
11 OrganiseIT 0
14 Templates 9
15 Superfund 14
16 Partnership 14
37 Meetings 30
49 Sick Leave 48
50 Long Service Leave 48
51 Holidays 48
52 Employment Contract 45
53 Correspondence 45
695 admin 1
698 Test 11
699 khalik 698
700 venu 698
701 Test 698

in the first table OIT_Permissions where all the Folder_Level=3 for those
records i need to get the matching record from OIT_Folder link is
OIT_Permissions.Folder_ID =OIT_Folder.Rec_ID and in next level
get all the record where folder_parent =rec_id

sample out put

Folder_ID folder_name
----------- -------------
11 OrganiseIT
9 Administration
1 Recycle Bins
695 admin
695 admin
14 Templates
15 Superfund
16 Partnership
699 khalik
700 venu
701 Test

thanks

======================================
Ask to your self before u ask someone

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-12-19 : 05:48:47

i meant to be cler may be i am long

======================================
Ask to your self before u ask someone

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-12-24 : 04:11:55

hi could any one please look into and say give some tips

======================================
Ask to your self before u ask someone

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2003-01-07 : 00:31:03

it has been long time......

a have gone through search but nothing...
please some one should have a tip

select * from oit_folder where rec_id =1
this return the one record... with this rec_id as 1
i can have several sub folders that i can get by

select * from oit_folder where folder_parent =1

in similar way i need to get the all the childs for all the record and this job should loop till end of the tree...

a simple example of tree view (windows explorer)

======================================
Ask to your self before u ask someone

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-01-07 : 07:23:03

You've chosen the wrong model for a tree of unknown depth. Read More Trees & Hierarchies in SQL. If you keep your adjacency model, you will need an iterative process to walk the tree and SQL is probably not your best choice for that task.

Jay White
{0}

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2003-01-08 : 07:04:48

thanks a lot for the link...
i found the expanding hierarchies in BOL

regards

======================================
Ask to your self before u ask someone

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources