Select records NOT in a set

[backseat]

I have a list of members that I want to schedule for certain duties. One table lists the people:

db.define_table(

    'members',

    Field('fullname', type='string', label='Name'),
    [...]
)

I select members from that table according to certain criteria, but I want to not select them if they are unavailable on a given date.

I have:

db.define_table(
    'unavailable',
    Field('the_date', type='date', label='Date'),
    Field('who', type='reference members', label='Name')
)

It is possible, given a date and the other selection criteria, to get a list of available members for the date in question?

[Massimo Di Pierro]

s = datetime.date(2020,1,1)

subquery = db(db.unavailable.the_date==s)._select(db.unavailable.who) # notice _select

db(~db.members.id.belongs(subquery)).select(db.members.ALL)

[backseat]

Fabulous. Thanks Massimo!