Postgres Complex Query to get column values having corresponding column joined with another table having a specific value

Question

I have two Postgres tables:

1- relationships:

    | user_id | target_user_id |

2- affiliations:

    | user_id | user_type_id | current |

user_id from affiliations can be any of the two column values in relationships, and current in affiliations is a boolean value.

In relationships, user_id is not unique and can have multiple corresponding target_user_id values.

I want to get from affiliations a list of user_id that are also in the user_id column in relationships, and have all their corresponding target_user_id values have their 'current' value in affiliations set as false

Example:

relationships:

user_id | target_user_id

    1   |     11
    1   |     12
    1   |     13

    2   |     14
    2   |     15
    2   |     16

affiliations:

user_id | current

    1   |     true
   11   |     false
   12   |     false
   13   |     false

    2   |     false
   14   |     true
   15   |     false
   15   |     false

so I want the query to return 1 only, since user 2 doesn't have all its corresponding target_user_id having their current as false

Thanks in advance!

Answer 1

Ok i finally constructed the right query as follows:

UPDATE app.affiliations
SET current = true
where current = false
and user_id in (select r.user_id
                from app.affiliations as a join app.relationships as r
                on r.target_user_id = a.user_id
                group by r.user_id
                having false = ALL(array_agg(a.current))
               )