CODEWITHSUNDEEP
sqlsql-serveroracle-database
ako
ako

Reputation: 420

SQL query to select rows with all ACL that user has

Suppose I have following table RIGHTS with data:

ID      NAME        OWNER_ID    ACL_ID  ACL_NAME
--------------------------------------------------
100     Entity_1    1           1       g1
100     Entity_1    2           2       g2
100     Entity_1    3           3       g3
200     Entity_2    1           1       g1
200     Entity_2    2           2       g2
300     Entity_3    1           1       g1
300     Entity_3    2           2       g2
300     Entity_3    4           NULL    NULL
400     Entity_4    1           1       g1
400     Entity_4    2           2       g2
400     Entity_4    3           3       g3
400     Entity_4    4           NULL    NULL
500     Entity_5    4           NULL    NULL
500     Entity_5    5           NULL    NULL
500     Entity_5    6           NULL    NULL
600     Entity_6    NULL        NULL    NULL

How to select all (ID, NAME) records for which there is no even single ACL_ID=NULL row except those rows with OWNER_ID=NULL. In this particular example I want to select 3 rows:

For now I use SQL Server, but I want it works on Oracle as well if it possible.

UPDATE I apologize I had to mention that this table data is just a result of a query with joins, so it has to be taken into account:

SELECT DISTINCT
 EMPLOYEE.ID
 ,EMPLOYEE.NAME
 , OWNERS.OWNER_ID as OWNER_ID
 , GROUPS.GROUP_ID as ACL_ID
 , GROUPS.NAME as ACL_NAME

from EMPLOYEE

inner join ENTITIES on ENTITIES.ENTITY_ID = ID
left outer join OWNERS on (OWNERS.ENTITY_ID = ID and OWNERS.OWNER_ID != 123)
left outer join GROUPS on OWNERS.OWNER_ID = GROUPS.GROUP_ID

where 
ENTITIES.STATUS != 'D'

Upvotes: 0

Views: 1951

Answers (3)

San
San

Reputation: 4538

Using simple filters in where clause:

with tab(ID,NAME,OWNER_ID,ACL_ID,ACL_NAME) as (
select 100, 'Entity_1', 1,1, 'g1' from dual union all
select 100, 'Entity_1', 2,2, 'g2' from dual union all
select 100, 'Entity_1', 3,3, 'g3' from dual union all
select 200, 'Entity_2', 1,1, 'g1' from dual union all
select 200, 'Entity_2', 2,2, 'g2' from dual union all
select 300, 'Entity_3', 1,1, 'g1' from dual union all
select 300, 'Entity_3', 2,2, 'g2' from dual union all
select 300, 'Entity_3', 4,NULL, NULL from dual union all
select 400, 'Entity_4', 1,1, 'g1' from dual union all
select 400, 'Entity_4', 2,2, 'g2' from dual union all
select 400, 'Entity_4', 3,3, 'g3' from dual union all
select 400, 'Entity_4', 4,NULL,NULL from dual union all
select 500, 'Entity_5', 4,NULL,NULL from dual union all
select 500, 'Entity_5', 5,NULL,NULL from dual union all
select 500, 'Entity_5', 6,NULL,NULL from dual union all
select 600, 'Entity_6', NULL,NULL,NULL from dual)
--------------------------------
---End of data preparation here
--------------------------------
select a.id, a.name
  from tab a
 where ((a.ACL_ID is not null and a.ACL_NAME is not NULL) or a.OWNER_ID is null)
   and not exists (select 'x' 
                     from tab b 
                    where b.id = a.id 
                      and (b.ACL_ID is null or  b.ACL_NAME is null) 
                      and b.owner_id is not null)
 group by a.id, a.name;

Output:

ID  NAME
------------
200 Entity_2
100 Entity_1
600 Entity_6

But I still wonder, what would be you logic where there is data like :

ID      NAME        OWNER_ID    ACL_ID  ACL_NAME
--------------------------------------------------
600     Entity_1    null        null    null
600     Entity_1    2           null    null

??????????

Upvotes: 0

CodeNewbie
CodeNewbie

Reputation: 2091

Here's my solution on Oracle.

SELECT DISTINCT
EMPLOYEE.ID
,EMPLOYEE.NAME
, OWNERS.OWNER_ID as OWNER_ID
, GROUPS.GROUP_ID as ACL_ID
, GROUPS.NAME as ACL_NAME

from EMPLOYEE

inner join ENTITIES on ENTITIES.ENTITY_ID = ID
left outer join OWNERS on (OWNERS.ENTITY_ID = ID and OWNERS.OWNER_ID != 123)
left outer join GROUPS on OWNERS.OWNER_ID = GROUPS.GROUP_ID

where ENTITIES.STATUS != 'D'
and EMPLOYEE.ID not in (select id from EMPLOYEE 
                        where GROUPS.GROUP_ID is null
                        and OWNERS.OWNER_ID is not null);

You simply need to append the inner subquery from my earlier answer and you will get your solution.

Upvotes: 0

shree.pat18
shree.pat18

Reputation: 21757

Try this:

select s.id, s.name
from
(select id,name,max(coalesce(owner_id,-1)) owner_id, min(coalesce(acl_id,-1)) acl_id
 from yourtable
 group by id,name) as s
where s.owner_id = -1
or (s.owner_id > -1 and s.acl_id > -1)

We use COALESCE to default null values to -1 (assuming the columns are integers), and then get the minimum values of owner_id and acl_id per unique id-name combination. If the maximum value of owner_id is -1, then the owner column is null. Likewise, if minimum value of acl_id is -1, then at least one null valued row exists. Based on these 2 conditions, we filter the list to get the required id-name pairs. Note that in this case, I simply chose -1 as the default value because I assume you don't use negative numbers as IDs. If you do, you can choose a suitable, "impossible" value as the default for the COALESCE function.

This should work on SQL Server and Oracle.

Upvotes: 1

Related Questions