SELECT WHERE multiple records don’t exist

I have the following two tables.

attempts:

id
40
41
42
43

attempt_tags:

id    attemptid    tagid
1     40           2
2     42           4
3     40           11
4     43           10
5     44           2

I’m looking to select a record from the attempts table where (for example) tagid 2 and 11 are BOTH not present, so the result of the query in this example would return everything except for id 40.

I initially thought this question would be a good solution, but I’m not as convinced now, I imagine in the scenario where I need to select the results that don’t have many tagid‘s present (e.g. 2, 11, 15, 18, 20, 25 etc), this query would end up getting quite long.

I’m sure there’s a simple solution, but I’m just not seeing it at the moment.

Answer

One method uses not exists, twice:

select a.*
from attempts a
where not exists (select 1
                  from attempt_tags att
                  where att.attemptid = a.id and
                        att.tagid = 2
                 ) and
      not exists (select 1
                  from attempt_tags att
                  where att.attemptid = a.id and
                        att.tagid = 11
                 ) ;
  

EDIT:

You can also simplify this to:

select a.*
from attempts a
where not exists (select 1
                  from attempt_tags att
                  where att.attemptid = a.id and
                        att.tagid in (2, 11)
                 ) ;