CODEWITHSUNDEEP
phpsqlmysql
Dom
Dom

Reputation: 1028

Pulling most recent result from a one to many join in mysql

I am working on a web app and I have a database with two tables. One of them is called entities and the other is activities. It is a one to many relationship where there are many entities in the activities table. The activities table has a date associated with it and I want to pull results based on certain entity id's but I only want to pull the most recent activity (by its date). So basically, I only want one return per entity. There must be a way to do this in mysql without parsing out the data with php. I want to make the app as fast as possible. Any help would be greatly appreciated.

Upvotes: 0

Views: 311

Answers (2)

Jshee
Jshee

Reputation: 2686

I would say, create a linking table through a PK / FK relationship.

So for instance: [entities table] ------ [ent_activities] --- [activities] , especially since the two data is so intertwined.

1:M is a horrible data integrity issue and even more so when you have 10s, even hundreds of 1:M throughout.

Good luck

Upvotes: 0

Conrad Frix
Conrad Frix

Reputation: 52645

Guessing at field names ...

SELECT 
 e.*, a.*
FROM 

(SELECT 
   MAX(ActivityID) ActivityID,
   EntityID
FROM 
   Activity
GROUP By
  EntityID) maxActivity

INNER JOIN Activity a
ON maxActivity.ActivityID = a.ActivityID
INNER JOIN Entity e
ON e.EntityID = a.EntityID

Or if ID isn't always the latest and you really want date (assuming two activities can't share the same date)

    SELECT 
     e.*, a.*
    FROM
 (SELECT 
       MAX(Date) Date, 
       EntityID
    FROM 
       Activity
    GROUP By
      EntityID) maxActivity

    INNER JOIN Activity a
    ON maxActivity.Date = a.Date
       and maxActivity.EntityID = a.EntityID 
    INNER JOIN Entity e
    ON e.EntityID = a.EntityID

Upvotes: 1

Related Questions