CODEWITHSUNDEEP
mysqlsqlgroup-bysql-order-bymin
Jerodev
Jerodev

Reputation: 33186

Get the lowest price for a product from a table of product prices per store

I am making a website to compare product prices between different stores. I have created a search function where I want to display the current lowest price and this is where I am a bit stuck.

I have a table Products with the basic information and a table product_store with the prizes in different stores for different products. Below is a basic schema of the database for these tables:

+-----------+   +---------------+
| products  |   | product_store |
+-----------+   +---------------+
| id        |   | product_id    |
| name      |   | store_id      |
+-----------+   | price         |
                | created_at    |
                +---------------+

The product_store table has multiple prices for the same product_id and store_id so I can create a price history.

Now I would like to create a query to get all products and their lowest price at the moment. So, you take the price for each store with the highest created_at, and from this collection I want to get the lowest value.

This is what I have tried so far:

select products.*, prices.price 
from products
left join (
    SELECT p1.product_id, min(p1.price) as price, p1.created_at 
    FROM product_store p1 
    WHERE p1.created_at = (SELECT max(created_at) FROM product_store p2 WHERE p2.store_id = p1.store_id
) as prices
on prices.product_id = products.id

I search for the highest created_at per store and product and get the lowest price for these rows. However this gives some very strange results where the prices get mixed up between the products and some products that have prices don't have any in the results.

Can someone help me to create a good query for this problem?

Thanks in advance. :)

Upvotes: 1

Views: 4722

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Here is one method. It aggregates the product_store table to get the maximum created date for each store. Then it joins it back to that table to get price and finally does an aggregation in the outer query:

select p.*, min(ps.price)
from products p left join
     (select ps.product_id, ps.store_id, max(created_at) as maxca
      from product_store ps
      group by ps.product_id, ps.store_id
     ) pssum
     on pssum.product_id = p.id left join
     product_store ps
     on ps.product_id = pssum.product_id and
        ps.store_id = pssum.store_id and
        ps.created_at = pssum.maxca
group by p.id;

Your product_store table is an example of a slowly changing dimension. If it were set up with a eff_date and end_date (effective date and end date) columns, then the query would be easier to write and probably more efficient in terms of performance.

Upvotes: 2

Related Questions