Check if Item has Data

Question

I have a table that contains a whole lot of fields.

What I am trying to do is see if any items are a missing certain fields.

Example of data:

+--------+----------+-------+
| ITEMNO | OPTFIELD | VALUE |
+--------+----------+-------+
| 0      | x        | 1     |
+--------+----------+-------+
| 0      | x        | 1     |
+--------+----------+-------+
| 0      | x        | 1     |
+--------+----------+-------+
| 0      | x        | 1     |
+--------+----------+-------+
| 0      | x        | 1     |
+--------+----------+-------+

There are 4 "OPTFIELD" which I want to see if all "ITEMNO" have.

So the logic I want to apply is something along the lines of:

Show all items that do not have the "OPTFIELD" - "LABEL","PG4","PLINE","BRAND"

Is this even possible?

Answer 1

Your data makes no sense. From the description of your question, it looks like you want itemno that do not have all 4 optfields. For this, one method uses aggregation:

select itemno
from mytable
where optfield in ('LABEL', 'PG4', 'PLINE', 'BRAND')
group by itemno
having count(*) < 4

On the other hand, if you want to exhibit all missing (itemno, optfield) tuples, then you can cross join the list of itemnos with a a derived table with of optfields, then use not exists:

select i.itemno, o.optfield
from (select distinct itemno from mytable) i
cross join (values ('LABEL'), ('PG4'), ('PLINE'), ('BRAND')) o(optfield)
where not exists (
    select 1 
    from mytable t
    where t.itemno = i.itemno and t.optfield = o.optfield
)