rewards the products qualify for

Question

products purchased
--------------------------
bana
bana
bana
stra
kiwi

reward requirements table (related to a rewards table)
reward id, products
----------------------
1,bana
1,bana
1,bana
2,stra
2,bana
3,stra
4,cart
5,bana
5,bana
5,oliv

Can you help me with sql to get rewards the products purchased qualifies for?

In the case above the reward ids would be:

1
2
3

If there is a better design that would make the solution easier I welcome those as well. I'm using product names for the sake of easier explaining, I hope. (I'll replace with product ids later)

Answer 1

With this schema:

CREATE TABLE            product
(
    product_id          int IDENTITY
    , name              varchar(50)
)

CREATE TABLE            requirement
(
    requirement_id      int IDENTITY
    , product_id        int
    , quantity          int
    , reward_id         int
)

CREATE TABLE            reward
(
    reward_id           int IDENTITY
    , reward            varchar(50)
)

CREATE TABLE            purchased
(
    purchased_id        int IDENTITY
    , product_id        int
    , quantity          int
)

Your query becomes:

SELECT      requirement.reward_id
FROM        requirement
            LEFT JOIN purchased
                ON  purchased.product_id = requirement.product_id
                AND purchased.quantity >= requirement.quantity
GROUP BY    requirement.reward_id
HAVING      COUNT(purchased.product_id) = COUNT(requirement.reward_id);

Here's a SQLFiddle to play around with: http://sqlfiddle.com/#!3/e93c9/7

Answer 2

This query will solve your problem.

select r.reward_id
from (
  select reward_id, product, count(*) needed
  from reward_requirements
  group by reward_id, product
) r
left join (
  select product, count(*) bought
  from products_purchased
  group by product 
) p on r.product=p.product and p.bought >= r.needed
group by r.reward_id
having count(reward_id) = count(distinct p.product)
order by r.reward_id

To make your design better, you could redo the reward_requirements to have the columns (product, needed) instead of having to list it multiple times. It will also get rid of the first subquery.