How do I write a query to exclude groups of records if at least one record in the group meets a certain condition?

I need to write a query that selects records from a table only if there is no record within a group of records with a specific property.

To put this in context, the table is offers (as in real estate). There can be multiple offers with the same id. The id is what defines the grouping. Another field is status, which can be ‘pending’, ‘countered’, ‘accepted’, ‘rejected’, ‘expired’, ‘draft’. Another field is the id of the user. So let’s say a buyer made an offer. A new offer would be created in the database with the status ‘pending’ and the id of the buyer. Then the seller might decide to counter offer. This would insert another record with the same offer id but a status of ‘countered’ and the user id of the seller. This would go on until the offer is either ‘accepted’ or ‘rejected’ or ‘expired’.

I want to write query that selects all offers that are not rejected and not expired and not draft.

The best I can think of is this:

select distinct offer_id 
from offers
where 
    listing_id = ${listingId} 
    and offer_id not in (
        select offer_id 
        from offers 
        where status = 'Rejected' or status = 'Expired' or status = 'Draft'
    )

This works but the nested select makes it really inefficient. I would like to exclude the status of rejected, expired, and draft using joins instead. Is that possible?

Thanks

Answer

If I followed you correctly, you can aggregate by offer_id and use a having clause to filter out offers whose any status is either rejected, expired or draft:

select offer_id
from offers
where listing_id = ? 
group by offer_id
having not bool_or(status in ('Rejected', 'Expired', 'Draft'))