How to find exact matching values in sql-CodePudding

How can I find the exact match in case of duplicate values in SQL I am using the below query to find customers which are having write access based on the below values.

user **A write** user - {UI.ACCESS, API.ACCESS} and
user **B read** user - {UI.ACCESS, API.ACCESS, UI.READONLY, API.READONLY}

Query -

select  ul.USERNAME,sp.PERMISSION_N,cus.ID 
from CUSTOMER cus 
    join USER_L ul on cus.ID = ul.CUSTOMER_ID 
    join USER_ROLE ur on ul.ID = ur.USER_ID 
    join SECURITY_ROLE sr on ur.SECURITY_ROLE_ID = sr.SECURITY_ROLE_ID 
    join SECURITY_PERMISSION sp on sr.SECURITY_PERMISSION_ID = sp.ID 
where sp.PERMISSION_NAME in ('UI.ACCESS','API.ACCESS')

above query return the B user as well but I am expecting only A.

CodePudding user response：

You want to check across all the rows in the SECURITY_PERMISSION table that none of them have the forbidden roles.

You have tagged both Oracle and MySQL. In Oracle, you can use:

SELECT ul.USERNAME,
       sp.permission_names,
       cus.ID 
FROM   CUSTOMER cus 
       INNER JOIN USER_L ul
       ON (cus.ID = ul.CUSTOMER_ID)
       INNER JOIN USER_ROLE ur
       ON (ul.ID = ur.USER_ID)
       INNER JOIN SECURITY_ROLE sr
       ON (ur.SECURITY_ROLE_ID = sr.SECURITY_ROLE_ID)
       INNER JOIN (
         SELECT id,
                LISTAGG(permission_name, ',')
                  WITHIN GROUP (ORDER BY permission_name)
                  AS permission_names
         FROM   SECURITY_PERMISSION sp
         GROUP BY id
         HAVING COUNT(
                  CASE
                  WHEN PERMISSION_NAME in ('UI.ACCESS','API.ACCESS')
                  THEN 1
                  END
                ) > 0
         AND    COUNT(
                  CASE
                  WHEN PERMISSION_NAME in ('UI.READONLY', 'API.READONLY')
                  THEN 1
                  END
                ) = 0
       )
       ON (sr.SECURITY_PERMISSION_ID = sp.ID)

For MySQL, you would need to replace LISTAGG with something to aggregate strings such as GROUP_CONCAT.

CodePudding user response：

Add

AND sp.PERMISSION_NAME NOT IN ('UI.READONLY', 'API.READONLY')

to exclude the additional permissions of B.