MySQL - Get three latest dates from tables-CodePudding

I have two tables:

create table product (
productid int,
productname varchar(100),
primary key(productid)
)engine=innodb;

create table purchase (
purchaseid int,
fproductid int,
customerage int,
purchasedate date,
purchasetime time,
primary key(purchaseid, fproductid),
foreign key(fproductid) references product(productid)
)engine=innodb;

Now I want to get the three latest purchases and the age of the customers who bought them. I've tried this:

select * 
from product, purchase
where productid = fproductid
and purchasetime in 
 (select max(purchasetime)
 from purchase
 where year(purchasedate) = year(now())
 group by purchaseid
 order by purchasetime)
order by purchasetime limit 3;

I'm not getting the correct results with this.

What am I doing wrong? And also is there a way to get them in chronological order from the latest of the three to the earliest?

order by purchasetime desc limit 3;

is not working for me.

Thank you!

CodePudding user response：

You need to understand relationships. The query could be much simpler:

SELECT * FROM purchase JOIN product ON productid = fproductid
ORDER BY purchasetime DESC LIMIT 3;

Note: if purchaseid is an auto_increment PK, it's more efficient to ORDER BY purchaseid instead of time

One of the reasons why your current query is not returning the results you expect is because of and purchasetime in (select max(purchasetime)...

MAX() is returning only one value, so you will get at most one result (unless more orders were made in the same second)