Home » SQL & PL/SQL » SQL & PL/SQL » Interesting SQL Nesting query
Interesting SQL Nesting query [message #270651] Thu, 27 September 2007 13:41 Go to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
Hi,
Here are the tables.

folder_tbl:
----------
Columns: folderid,parentid,name
100,-200,FOLDER1

folder_rights:
---------------
Columns: folderid, rightid, perms
100,1111,109394
100,1222,112323
100,1333,113333

usergrp_tbl:
columns: id,name,type
1111,Group1,1
1222,Group2,1
1333,Group3,1
1444,User1,0
Note: if type=0 it is group, 0=user

usergrp_children:
columns: id,childid
1111,1333

Here Folder1 is a folder and all the Group<Nos> & User<nos> are the permissions on the folder.
Now I want to find out how many users (count) are having permissions on this folder.
Note that a user can present in more than 1 group within its nesting but need to count only once. For example: User1 was added given permission directly to folder1 and also present in Group3.

In “folder_rights” table, the rightid value can be id of group or folder.


see attached document.

any ideas?

Thanks,
mahesh

[Updated on: Thu, 27 September 2007 13:54]

Report message to a moderator

Re: Interesting SQL Nesting query [message #270656 is a reply to message #270651] Thu, 27 September 2007 13:47 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:

see attached document.


No. Please copy and paste it and use formating as per guidelines
TA
Re: Interesting SQL Nesting query [message #270660 is a reply to message #270651] Thu, 27 September 2007 13:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Noone will download a doc file.
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Post a test case, if you want us to try something.
Explain with result and words.

Regards
Michel

[Updated on: Thu, 27 September 2007 13:50]

Report message to a moderator

Re: Interesting SQL Nesting query [message #270661 is a reply to message #270651] Thu, 27 September 2007 13:49 Go to previous messageGo to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
i have explained my problem in a pictorial format which is easy to interpret.

Oracle version:10g (10.1.0.2.0).

[Updated on: Thu, 27 September 2007 13:50]

Report message to a moderator

Re: Interesting SQL Nesting query [message #270662 is a reply to message #270661] Thu, 27 September 2007 13:50 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
OK, but you'll be hard pushed to find someone who will open an unknown doc. Good luck Thumbs Up
Re: Interesting SQL Nesting query [message #270664 is a reply to message #270661] Thu, 27 September 2007 13:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL is not paint.
If you can't explain it with words, how could you express it in SQL?

Regards
Michel
Re: Interesting SQL Nesting query [message #270669 is a reply to message #270651] Thu, 27 September 2007 14:01 Go to previous messageGo to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
updated my initial message..

Thanks,
Mahesh
Re: Interesting SQL Nesting query [message #270682 is a reply to message #270669] Thu, 27 September 2007 14:26 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Updated with what?
I don't see any formatting nor test case (create table and insert statements).

Regards
Michel

[Updated on: Thu, 27 September 2007 14:27]

Report message to a moderator

Previous Topic: check constraint involving two column values
Next Topic: altering the table
Goto Forum:
  


Current Time: Sun Dec 11 08:13:58 CST 2016

Total time taken to generate the page: 0.04756 seconds