CODEWITHSUNDEEP
phpmysql
Fero
Fero

Reputation: 13315

how to retrieve the last updated data from multiple categories using php and mysql

How to retrieve the last updated data from multiple 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.

Upvotes: 0

Views: 96

Answers (1)

Jeff Swensen
Jeff Swensen

Reputation: 3573

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)

Upvotes: 1

Related Questions