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

Question

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.

... and one of the users happen to also have a CLIENT_ADMIN role on any other business, I want to skip <<-- You could use ... AND NOT EXISTS(...) — wildplasser
– wildplasser, Commented Apr 4, 2022 at 12:51
BTW: It would help if you formulated your requirements in a more exact manner. — wildplasser
– wildplasser, Commented Apr 4, 2022 at 13:17

Andronicus · Accepted Answer · 2022-04-04 13:39:17Z

1

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' 
  )
)

edited Apr 4, 2022 at 13:39

answered Apr 4, 2022 at 12:57

Andronicus

26.2k18 gold badges55 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mankind1023 Over a year ago

Hi, this does not return any results no matter what I try for some reason.

Andronicus Over a year ago

@Mankind1023 thanks for the input, please try again, I've changed imt.business_id to mt.business_id in the inner select to fetch appuser_ids

Mankind1023 Over a year ago

still not working :S

Andronicus Over a year ago

@Mankind1023 please, try it now; I have created a db fiddle, so you can check it and write a query that does not return what you expect: dbfiddle.uk/…

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related