Product id missing attribute id

Question

I have a table with prodID , attrID Each prodID have 1-5 atrrID's I want to list the prodIDs which don't have a specific atrrID, eg "c"

+-----------+---------+
| prodID    | attrID  | 
+-----------+---------+
| 1         | a       | 
| 1         | b       |
| 1         | c       | 
| 2         | a       | 
| 2         | b       | 
| 2         | d       |
| 3         | b       | 
| 3         | c       | 
| 3         | d       |
| 4         | a       | 
| 4         | b       | 
| 4         | d       | 
| 4         | e       | 
| 5         | a       | 
| 5         | b       | 
| 6         | a       | 
| 6         | d       |   
+-----------+---------+

The result should be

+-----------+
| prodID    |
+-----------+
| 2         |
| 4         | 
| 5         |
| 6         |
+-----------+

Table name is rp_prod_attr_list

Answer 1

You can use group by and having:

select prodid
from mytable 
group by prodid
having max(attrid = 'c') = 0

In MySQL, a condition evaluated in numeric context returns 1 if fullfilled, else 0. So max(attr_id = 'c') = 0 phrases as: there is no row in the group that satisfies the condition.

Answer 2

Try the following:

select distinct prodid
from prod p
where not exists (select null
                  from prod p_
                  where p_.prodid = p.prodid
                   and attrID = 'c')