how to retreive the last updated data from mulitple categories using php and mysql

Question

Hi all,

How to retreive the last updated data from mulitple categories using php and mysql.

For example:

Category Table:

id categoryName
1   test1
2   test2
3   test3

Product Table

id categoryId productName dateTime
1   1         product1    2010-05-06 10:00:37
2   1         product2    2010-05-06 10:10:41
3   2         product3    2010-05-06 10:20:53
4   2         product4    2010-05-06 10:22:44

Now, My o/p Should be

id categoryId productName dateTime
2   1         product2    2010-05-06 10:10:41
4   2         product4    2010-05-06 10:22:44

How can this be done using mysql query.

thanks in advance

Answer 1

SELECT p.id, c.id, p.productName, p.dateTime
FROM Category c, Product p
WHERE c.id = p.categoryId
AND p.id = (SELECT id FROM Product WHERE categoryId = c.id ORDER BY dateTime DESC LIMIT 1)