Postgres - select rows except where count is more than one and another condition-CodePudding

I have a table with the following sample data:

appuser_id     business_id     role_id
6              2               CLIENT_MANAGER
8              2               CLIENT_ADMIN
6              3               BUSINESS_MANAGER
8              3               BUSINESS_MANAGER
6              4               BUSINESS_MANAGER
8              5               BUSINESS_MANAGER
8              6               BUSINESS_MANAGER

I'm looking to create a function that pulls a list of all businesses for a user where his role is 'BUSINESS_MANAGER', however, if multiple people manage a business (business 3 above), and one of the users happen to also have a 'CLIENT_ADMIN' role on any other business, I want to skip that contended record (skip business 3 that is), basically, given this query:

SELECT * FROM my_table WHERE appuser_id = 8 AND role_id = 'BUSINESS_MANAGER' AND ???;

I want the following results:

appuser_id     business_id     role_id
8              5               BUSINESS_MANAGER
8              6               BUSINESS_MANAGER

and for the same query for user 6 I want:

appuser_id     business_id     role_id
6              3               BUSINESS_MANAGER
6              4               BUSINESS_MANAGER

I have no idea where to begin something like this.

If anyone has a better title, please feel free to change.

CodePudding user response：

As it was suggested in the comment, you can use not exists:

select * 
from my_table mt
where mt.appuser_id = 6
and mt.role_id = 'BUSINESS_MANAGER' 
and mt.business_id not in (
  select imt.business_id
  from my_table imt
  where exists (
    select *
    from my_table iimt
    where imt.appuser_id <> iimt.appuser_id
    and iimt.appuser_id = mt.appuser_id
    and iimt.role_id = 'CLIENT_ADMIN' 
  )
)