Combine latest records with table Code Answer

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of Combine latest records with table without wasting too much if your time.

The question is published on by Tutorial Guruji team.

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.

We are here to answer your question about Combine latest records with table - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji