2

I'm trying to work out a query that can parse through a table of prices (and exceptions to those prices) to yield the applicable price for a product, ideally using a join. I'm on PostgreSQL. For example, consider a teddy bear store. The default teddy bear price is $20. If, however, the teddy bear is at least 12 inches tall, we want to charge $30. If the teddy bear is made of suede, we charge $40, and if the teddy bear is both at least 12 inches tall and made of suede, we charge $60 (because the larger bear also requires more of the expensive fabric). ETA: We now also have a grass bear special because we just hired a teddy bear maker who was previously a florist. Given the perishable nature of the bear, it costs $15 if it's at least 24 inches tall.

Here's what the prices table I have looks like (sorry about the formatting):

EDITED: I added the grass bear to better reflect my inability to make any assumptions about the data. I simply need to pick the price that best corresponds to each product without knowledge of how different features affect the price, positively or negatively.

  price | min_ht | fabric 
---------------------------
| 20    | NULL   | NULL   |
---------------------------
| 30    | 12     | NULL   |
---------------------------
| 40    | NULL   | suede  |
---------------------------
| 60    | 12     | suede  |
---------------------------
| 15    | 24     | grass  | <-- our floral special
---------------------------

which I want to join with a products table like

  id    | height | fabric
---------------------------
| 1     | 8      | cotton |
---------------------------
| 2     | 6      | poly   |
---------------------------
| 3     | 4      | suede  |
---------------------------
| 4     | 18     | cotton |
---------------------------
| 5     | 12     | suede  |
---------------------------
| 6     | 30     | grass  |
---------------------------

to yield

  id    | height | fabric | price 
-----------------------------------
| 1     | 8      | cotton | 20    |
-----------------------------------
| 2     | 6      | poly   | 20    |
-----------------------------------
| 3     | 4      | suede  | 40    |
-----------------------------------
| 4     | 18     | cotton | 30    |
-----------------------------------
| 5     | 12     | suede  | 60    |
-----------------------------------
| 6     | 30     | grass  | 15    |
-----------------------------------

Any ideas? I saw a similar question, though it is unfortunately both unanswered and regarding MySQL.

0
1

This makes some assumptions but it will do:

SELECT
    prd.id, prd.height, prd.fabric,
    MAX(pri.price) AS price
FROM
    products AS prd
  JOIN
    prices AS pri
      ON  (prd.height >= pri.min_ht OR pri.min_ht IS NULL)
      AND (prd.fabric =  pri.fabric OR pri.fabric IS NULL) 
GROUP BY
    prd.id ;

The above assumes that the more details a pricing has, the higher the price is. Since that is not the case, a different, more general approach:

SELECT
    prd.id, prd.height, prd.fabric,
    pri.price
FROM
    products AS prd
  , LATERAL
    ( SELECT p.price
      FROM prices AS p
      WHERE (prd.height > p.min_ht OR p.min_ht IS NULL)
        AND (prd.fabric = p.fabric OR p.fabric IS NULL) 
      ORDER BY
          ((p.min_ht IS NOT NULL)::int + (p.fabric IS NOT NULL)::int) DESC
      LIMIT 1
    ) AS pri ;

Tested at SQL-Fiddle

1
  • It could be written with some window functions (or DISTINCT ON) in previous versions. That's why I asked for your version. Oct 9 '14 at 1:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.