Combine latest records with table

I am using mysql and I am having the following two tables:

Products:

| id | name      |
|----|-----------|
| 1  | Product 1 |
| 2  | Product 2 |
| 3  | Product 3 |

Prices:

| id | Product_id | created_at          | prices |
|----|------------|---------------------|--------|
| 1  | 1          | 2017-12-23 08:32:11 | 10     |
| 2  | 1          | 2017-11-21 03:33:10 | 12     |
| 3  | 2          | 2017-12-23 08:32:11 | 43     |
| 4  | 2          | 2017-11-21 03:33:10 | 23     |
| 5  | 3          | 2017-12-23 08:32:11 | 78     |
| 6  | 3          | 2017-11-21 03:33:10 | 34     |

If I do:

SELECT * FROM prices WHERE prices.id IN ( SELECT MAX(prices.id) FROM prices GROUP BY prices.created_at ) 

This gives me the last prices for each product_id:

| id | Product_id | created_at          | prices |
|----|------------|---------------------|--------|
| 1  | 1          | 2017-12-23 08:32:11 | 10     |
| 3  | 2          | 2017-12-23 08:32:11 | 43     |
| 5  | 3          | 2017-12-23 08:32:11 | 78     |

I would like to have as final result all Products with the latest prices, which would look like the following:

| id | name      | product_id | created_at          | prices |
|----|-----------|------------|---------------------|--------|
| 1  | Product 1 | 1          | 2017-12-23 08:32:11 | 10     |
| 2  | Product 2 | 2          | 2017-12-23 08:32:11 | 43     |
| 3  | Product 3 | 3          | 2017-12-23 08:32:11 | 78     |

However, I am not sure how to combine:

select * from products

and

SELECT * FROM prices WHERE prices.id IN ( SELECT MAX(prices.id) FROM prices GROUP BY prices.created_at ) 

I highly appreciate your replies!

Answer

Assuming your first query is giving you expected result, just use join.

SELECT p.*,d.name FROM prices p
inner join product d 
on d.id=p.product_id
WHERE 
p.id IN 
    ( SELECT MAX(prices.id) FROM prices GROUP BY prices.created_at ) 

If you believe that there are product id in price which are not mapped in product then use left join instead of inner join.

Leave a Reply

Your email address will not be published. Required fields are marked *