CODEWITHSUNDEEP
mysqlsqldatabase
Kirk Fleming
Kirk Fleming

Reputation: 517

Identify a set for which I match all set members

I need to identify the price list that I should use for each product in a list of products. The correct price list to be used for a given product is determined by several product parameters which I already know. But, not all parameters apply to all price lists. Here's example 'criteria' for 3 price lists:

pricelist_key    param     value
A                size      n1
A                weight    n2
A                color     n4
B                size      n5
B                weight    n6
B                color     n8
B                type      n10
C                weight    n2
C                min_size  n5

The question to answer is this: "which set of criteria values, identified by pricelist_key, does my product meet"?

I've ensure that each set of parameter values is unique--in other words, each pricelist has a unique set of criteria and/or values--I cannot meet all criteria for more than one price list. I know in advance the complete set of parameters from which all price lists must be built, but any given price list may use as its criteria any subset of those parameters.

Procedurally, I'd loop through products, loop through price lists, and loop through parameters. The first parameter I missed would drop me out to the next price list until I found the one price list for which product meets all criteria. The next step is to simply look up a date in the identified price list and get the one price that is found there.

But I'm dealing with sets (in this context 'price list criteria sets') and I'd like to understand how to see this as a natural SQL problem--because it seems like it should be. I'm free to re-design the way the criteria are managed, this is simply the corner I painted myself into. The design goal was to handle the many varied parameters that can be used for defining price list criteria.

Suggestions?

Upvotes: 0

Views: 42

Answers (1)

piotrm
piotrm

Reputation: 12356

Assuming what we see is a part of the pricelist table for one given product and you need to find a pricelist_key that matches all criteria known beforehand you can use a query like this one to find it:

SELECT pricelist_key, count(*) as params_matched FROM pricelist
WHERE product_id = 1
AND 
(    param = 'weight' AND value = 'n6'
  OR param = 'size'   AND value = 'n5'
  OR param = 'color'  AND value = 'n8' )
GROUP BY pricelist_key
HAVING params_matched = 3

Upvotes: 1

Related Questions