Select distinct rows where all values are in group

Question

I have a table that stores a one to many relationsion (caseid to code). It is possible for one caseid to have many codes.

I would like to select all rows where all the codes for a given caseid are contained within a group of codes. If a caseid is associated with any code that is not in this group, then exclude it, regardless of if all the other codes are in the group.

I would then like to build a table where each unique caseid has a single row and four Boolean columns (one for each code I am looking for) denoting if that code is present.

Here is my query so far:

select distinct(caseid), _43280, _43279, _43282, _43281 from
(select caseid,   
  0 < countif(code = "43280") as _43280,
  0 < countif(code = "43279") as _43279,
  0 < countif(code = "43282") as _43282,
  0 < countif(code = "43281") as _43281
from mytable
inner join (
  select caseid, logical_and(code in ('43280', '43279', '43282', '43281')) as include,
  from mytable
  group by caseid
  having include
  ) 
using(caseid)
group by caseid
order by caseid)

An example table may be:

caseid | code
1         43280
1         43279
1         43282
2         43280
2         43279
2         43282
2         99999
3         43280
3         43279
3         43282

It should come out as:

caseid | _43280 | _43279 | _43282 | _43281
 1        TRUE     TRUE     TRUE     FALSE
 3        TRUE     TRUE     TRUE     FALSE

Answer 1

You can use conditional aggregation as follows:

select caseid,
    logical_or(code = 43280) code_43280,
    logical_or(code = 43279) code_43279,
    logical_or(code = 43282) code_43282,
    logical_or(code = 43281) code_43281
from mytable
group by caseid
having not logical_or(code not in (43280, 43279, 43282, 43281))