SQL Query doesn’t run as intended(between)

I have the following query(in postgresql),

select distinct 
    "bookings"."memberId" 
from "shifts" 
inner join "bookings" 
on "bookings"."shiftId" = "shifts"."id" 
where "shifts"."startTime" not between '2016-01-02 00:00:00.000' and '2020-01-01 23:59:59.999' 
    and "shifts"."startTime" >= '2015-01-01 00:00:00.000' 
    and "shifts"."startTime" <= '2016-01-01 23:59:59.999' 
    and "bookings"."state" in ('ACCEPTED')

I am trying to select some memberId’s that aren’t between 2016 and 2020 but are between 2015 and 2016, however, the query still returns a memberId which is actually between 2016 and 2020

Answer

You seem to want aggregation:

select b."memberId" 
from "shifts" s join
     "bookings" b
     on b."shiftId" = s."id" 
where s."startTime" >= '2015-01-01' and
      b."state" in ('ACCEPTED')
group by b."memberId"
having max(s."startTime") < '2016-01-01' ;

Note: this uses date/times based on the question rather than in your existing query. You may need to adjust them (I’m not sure why you have January 2nd, for instance in the WHERE clause).

This takes all rows on or after 2015, and aggregates by the member id. The HAVING clause selects only members whose maximum date is in 2015.