MySQL group by for missing values

Question

Given a table with the following data

ID Value
1  A
1  B
1  C
1  D
2  A
2  C
3  A

I would like to build a query that returns which values are missing from the id set based on value A being present. It can be assumed an ID for 'A' is always present. Result:

ID | B | C | D
2  | 0 | 1 | 0
3  | 0 | 0 | 0

The values are A, B, C, D. In this example all values are there for ID 1 but the table reports that B is missing for both given that A is a value for ID 2 and so on.

I have a query to return which ID's are missing for a given value but I have not found a way to join all three together:

select id
from table_1 
where  id not in (
    select id
    from table_1
    where value = 'B' #additional queries replacing 'B' with 'C' and 'D'
)  and value = 'A' 
order by id asc

Is it possible to combine those three separate queries in to a result table as I have laid out? I feel like this requires inner joins but have not been able to build out a query that works.

Answer 1

You could use conditional aggregation:

SELECT id,
  SUM(Value = 'A') AS a,
  SUM(Value = 'B') AS b,
  SUM(Value = 'C') AS c,
  SUM(Value = 'D') AS d
FROM tab
GROUP BY id;

DBFiddle Demo

Values list (A,B,C,D) has to be known in advance.

---

Skipping row if all values are present:

SELECT *
FROM (
  SELECT id, SUM(Value = 'A') AS a,
   SUM(Value = 'B') AS b,
   SUM(Value = 'C') AS c,
   SUM(Value = 'D') AS d
  FROM tab
  GROUP BY id
) sub
WHERE NOT (a>0 and b>0 and c>0 and d>0);

DBFiddle Demo2