Link to home
Start Free TrialLog in
Avatar of pothireddysunil
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,DAPERMISSIONS,DARIGHTID,DAACLTYPE,KID,KNAME,KTYPE,KGROUPID
1313021,Test Dev,1313021,16777215,1000,1,1000,Admin,0,1001
1313021,Test Dev,1313021,102535,1001,2,1001,DefaultGroup,1,
1313021,Test Dev,1313021,258207,12566340,0,12566340,EIS - Livelink,1,
1313021,Test Dev,1313021,258207,5307,0,5307,renditionrobot (Delete) 1,0,1001
1313021,Test Dev,1313021,36995,1670122,0,1670122,TestGroup,1,
1313021,Test Dev,1313021,36995,1365392,0,1365392,DocWorkflow,1,
1313021,Test Dev,1313021,258207,4088484,0,4088484,demo,0,4088483

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
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Which DB are you using - SQL SERVER or ORACLE ?
Avatar of pothireddysunil
pothireddysunil

ASKER

it is Oracle
Avatar of Vitor Montalvão
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.
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,19434864,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
ASKER CERTIFIED SOLUTION
Avatar of flow01
flow01
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
I verified the query and it's working fine.
Thanks flow01.