Imagine a situation where a customer buys products and is awarded free
articles if s/he buys predefined sets of products.
For example if s/he buys twenty articles, among them a "cereal bowl",
a "coffee mug" and a "orange juice bottle", then s/he bought a
"breakfast set" and is awarded a free toaster.
I need to store the "cereal", "coffe" and "orange" items on the
"rules" table. (There will be other composite rules as well --buying
"soap", "sponge", "shampoo" and "towel" would mean the customer
purchased a "shower set" and is awarded some other freebie).
Next I have to match the items on the customer's shopping cart, to the
"rules" table. The catch is that for a rule to match, EVERY one of its
records must join. (S/he must have bought all items (at least one of
each) to qualify and "match" the set of rules). I thought of using
count(*) to check # of items bought against number of rule parts, but
if the purchase includes just two "coffees" and one "cereal" I was
getting false positives.
No need to worry about matching more than one rule.
Any ideas on how to accomplish this?
Any pointers immensely appreciated! Thanks in advance!