Quote
In the example you put, each retailer appears to have a single flavour.
No, it doesn't.
Look again:
+-------------+-----+---------+
| Retailer | PoS | Flavour |
+-------------+-----+---------+
| 7 eleven | 28 | 11 | ) 1 retailer, 2 flavours
| 7 eleven | 28 | 33 | )
| Family mart | 45 | 22 |
| Walmart | 22 | 11 |
| Carrefour | 200 | 11 | ) 1 retailer, 2 flavours
| Carrefour | 200 | 44 | )
+-------------+-----+---------+
Both "7 eleven" and "Carrefour" have two flavours, "Family mart" and "Walmart" have only one.
Multiple flavours for a given retailer are held in multiple
rows, not multiple
columns.
Quote
... I would like to know, how many retailers have chocolate (regardless of weather they also have other flavours) ...
That's done with a simple "where" clause on Flavour:
select Retailer
from stock
where Flavour = 11 // the id for Chocolate
order by Retailer ;
+-------------+
| Retailer |
+-------------+
| 7 eleven |
| Carrefour |
| Walmart |
+-------------+
Quote
... also I would like to know how many retailers have both chocolate and strawberry.
OK,
slightly more complex, but this should do it:
select Retailer
from stock
where Flavour in ( 11, 44 )
group by Retailer
having count( * ) = 2
order by Retailer ;
+-------------+
| Retailer |
+-------------+
| Carrefour |
+-------------+
Regards, Phill W.