pothireddysunil
asked on
SQL query question
Hi
I have 3 tables DTreeCore. DTREEACL, KUAF in my oracle database. Below is their PK and FK information.
DTreeCore Table:
DataID - PK
UserID - FK to KUAF.ID
GroupID - FK to KUAF.ID
DTreeACL:
DataID - PK and FK to DTreeCore.DataID
RightID - PK and FK to KUAF.ID
KUAF Table:
ID - PK
GroupID - ID of the users base Group
Here DTreeCore.DataID record(row) - it's the document information. Each document has user and group permissions. Each user and group permissions for that DataID(document) are stored in DTreeACL table.
For each DTreeCore.DataID - it will have more than one record in DTreeACL table.
the KUAF table consists of user and group information and if column Type = 0 then it is a User record and Type = 1 then it is a Group record.
When a record in DTreeACL table is a user record (KUAF.Type = 0) - I need that user information
and
when a record in DTreeACL table is a group record (KUAF.Type = 1) - then I need all the user records for that group from KUAF table (where select all records from KUAF where KUAF.GroupID=KUAF.ID).
I need all this information ( if its user then that information and if its a group then all the users of that group information with a query either with a union, join or subquery.
here is my sample querys -
query1 -
select
dc.DataID as dcID,
dc.Name as dcName,
da.DataID as daID,
DA.PERMISSIONS as daPermissions,
DA.RIGHTID as daRightID,
DA.ACLTYPE daACLType,
k.ID as kID,
K.NAME as kName,
K.Type as KType,
K.GROUPID as kGroupID
from CUSTOM.DTreeCore dc,custom.DTREEACL da, custom.KUAF k
where
dc.DATAID = da.DATAID
and DA.RIGHTID = K.ID
and dc.DataID = 1313021
Results of that sample query1 -
DCID,DCNAME,DAID,DAPERMISS IONS,DARIG HTID,DAACL TYPE,KID,K NAME,KTYPE ,KGROUPID
1313021,Test Dev,1313021,16777215,1000, 1,1000,Adm in,0,1001
1313021,Test Dev,1313021,102535,1001,2, 1001,Defau ltGroup,1,
1313021,Test Dev,1313021,258207,1256634 0,0,125663 40,EIS - Livelink,1,
1313021,Test Dev,1313021,258207,5307,0, 5307,rendi tionrobot (Delete) 1,0,1001
1313021,Test Dev,1313021,36995,1670122, 0,1670122, TestGroup, 1,
1313021,Test Dev,1313021,36995,1365392, 0,1365392, DocWorkflo w,1,
1313021,Test Dev,1313021,258207,4088484 ,0,4088484 ,demo,0,40 88483
From these results Type = 0 has GroupID are user records
Type=1 records has GroupID is null - these are groups - they are K.ID's = 1670122,1365392 are Groups - select user records from KUAF table for these groups
query2 -
select * from CUSTOM.KUAF k where K.GroupID in(1670122,1365392)
Results for the query2
KID,KNAME,KTYPE,KGROUPID
6685191,emacadie,0,1365392
10018860,nbk2p7b,0,1365392
1365393,nbk6w9m,0,1365392
1671014,nbkk63j,0,1670122
1670701,CORP\NBDX66R (Delete) 1670701,0,1670122
1714609,testuser,0,1670122
1721999,nbdx66r,0,1670122
I need some help in getting these results from both these quer1 and query2 in one query.
Thanks
I have 3 tables DTreeCore. DTREEACL, KUAF in my oracle database. Below is their PK and FK information.
DTreeCore Table:
DataID - PK
UserID - FK to KUAF.ID
GroupID - FK to KUAF.ID
DTreeACL:
DataID - PK and FK to DTreeCore.DataID
RightID - PK and FK to KUAF.ID
KUAF Table:
ID - PK
GroupID - ID of the users base Group
Here DTreeCore.DataID record(row) - it's the document information. Each document has user and group permissions. Each user and group permissions for that DataID(document) are stored in DTreeACL table.
For each DTreeCore.DataID - it will have more than one record in DTreeACL table.
the KUAF table consists of user and group information and if column Type = 0 then it is a User record and Type = 1 then it is a Group record.
When a record in DTreeACL table is a user record (KUAF.Type = 0) - I need that user information
and
when a record in DTreeACL table is a group record (KUAF.Type = 1) - then I need all the user records for that group from KUAF table (where select all records from KUAF where KUAF.GroupID=KUAF.ID).
I need all this information ( if its user then that information and if its a group then all the users of that group information with a query either with a union, join or subquery.
here is my sample querys -
query1 -
select
dc.DataID as dcID,
dc.Name as dcName,
da.DataID as daID,
DA.PERMISSIONS as daPermissions,
DA.RIGHTID as daRightID,
DA.ACLTYPE daACLType,
k.ID as kID,
K.NAME as kName,
K.Type as KType,
K.GROUPID as kGroupID
from CUSTOM.DTreeCore dc,custom.DTREEACL da, custom.KUAF k
where
dc.DATAID = da.DATAID
and DA.RIGHTID = K.ID
and dc.DataID = 1313021
Results of that sample query1 -
DCID,DCNAME,DAID,DAPERMISS
1313021,Test Dev,1313021,16777215,1000,
1313021,Test Dev,1313021,102535,1001,2,
1313021,Test Dev,1313021,258207,1256634
1313021,Test Dev,1313021,258207,5307,0,
1313021,Test Dev,1313021,36995,1670122,
1313021,Test Dev,1313021,36995,1365392,
1313021,Test Dev,1313021,258207,4088484
From these results Type = 0 has GroupID are user records
Type=1 records has GroupID is null - these are groups - they are K.ID's = 1670122,1365392 are Groups - select user records from KUAF table for these groups
query2 -
select * from CUSTOM.KUAF k where K.GroupID in(1670122,1365392)
Results for the query2
KID,KNAME,KTYPE,KGROUPID
6685191,emacadie,0,1365392
10018860,nbk2p7b,0,1365392
1365393,nbk6w9m,0,1365392
1671014,nbkk63j,0,1670122
1670701,CORP\NBDX66R (Delete) 1670701,0,1670122
1714609,testuser,0,1670122
1721999,nbdx66r,0,1670122
I need some help in getting these results from both these quer1 and query2 in one query.
Thanks
Which DB are you using - SQL SERVER or ORACLE ?
ASKER
it is Oracle
I've removed the other topics to attract only the right Experts (Oracle).
Create statements and sample data for the 3 tables would be very helpful. Expected results would be very helpful as well. Having the results of a sample query isn't very helpful. We cannot build a test case and give you a tested solution without good information to start with.
ASKER
John,
Here is the sample query's for 3 DataID's and sample data for those DataID's.
Sample Query's:
Query1 : select DataID,Name from CUSTOM.DTreeCore where DataID in (1313021,19331190,19434751 )
Query2 :select DataID,PERMISSIONS,RIGHTID ,ACLTYPE from custom.DTREEACL where DataID in (1313021,19331190,19434751 )
Query3 : select ID,NAME, Type, GROUPID from custom.KUAF k where k.ID in (select RIGHTID from custom.DTREEACL where
DataID in (1313021,19331190,19434751 ))
Query4 : select ID,NAME, Type, GROUPID from custom.KUAF k where k.GroupID in (12566340,19332686,1943486 4,19435308 ,19435309)
In stead of groups information, I need to get the list of users and users for the groups in a single query.
See the attachments for the sample data. Let me know.
Thanks.
Query1---DTreeCore.txt
Query2--DTreeACL.txt
Query3---KUAF-UsersandGroups.txt
Query4---KUAF-UsersfortheGroups.txt
Here is the sample query's for 3 DataID's and sample data for those DataID's.
Sample Query's:
Query1 : select DataID,Name from CUSTOM.DTreeCore where DataID in (1313021,19331190,19434751
Query2 :select DataID,PERMISSIONS,RIGHTID
Query3 : select ID,NAME, Type, GROUPID from custom.KUAF k where k.ID in (select RIGHTID from custom.DTREEACL where
DataID in (1313021,19331190,19434751
Query4 : select ID,NAME, Type, GROUPID from custom.KUAF k where k.GroupID in (12566340,19332686,1943486
In stead of groups information, I need to get the list of users and users for the groups in a single query.
See the attachments for the sample data. Let me know.
Thanks.
Query1---DTreeCore.txt
Query2--DTreeACL.txt
Query3---KUAF-UsersandGroups.txt
Query4---KUAF-UsersfortheGroups.txt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I did not test what flow01 posted, but I would recommend changing UNION to UNION ALL. There is unlikely to be duplicates and it isn't worth the sort operation to remove duplicates when you know there aren't any.
ASKER
I verified the query and it's working fine.
Thanks flow01.
Thanks flow01.