0

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.

Mark
  • 1,852
  • 3
  • 18
  • 31

2 Answers2

2

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)
                 ) ;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you - I think this would work, but surely there has to be a simpler way - there's the potential for I think around 20 different tagid's, so generating a query that has that many iterations of NOT EXISTS doesn't seem like the best solution – Mark Aug 02 '21 at 12:54
  • 1
    @MarkOverton . . . I added a simpler version. – Gordon Linoff Aug 02 '21 at 18:05
  • Hi, @GordonLinoff, is there a reason `select 1` is used? Would say `select id` work too? P.s I'm new to SQL. – daniel blythe Dec 22 '21 at 10:49
0

A possible solution is the following:

select *
  from attempts a
 where
 (select count(t.id)
    from attempts_tag t
   where t.attemptid = a.id
     and t.tagid in (2, 11)) = 0

union all

select *
  from attempts a
 where not exists
 (select 1
    from attempts_tag t
   where t.attemptid = a.id);
Kapitany
  • 1,319
  • 1
  • 6
  • 10