CODEWITHSUNDEEP
mysqljoinmax
arecaps
arecaps

Reputation: 173

MYSQL Join only on max row with data from multiple tables

I have a query that selects properties, and I need to join them to get the most recent activity on each one, where that activity_status = 3 (closed deal). when I get that, I need to get the bank that closed the deal (banks.is_reward = 1)

Problem is that the data is spread over many tables, so when I join to get all the results, and then try to limit to the max(activity_date), I need to group the results, and then I don't get the correct data from the other columns.

Here is a SQL Fiddle

I can do 

Select * from properties
join
(SELECT deal_properties.property_id, activity.deal_id, activity.activity_date, banks.bank_id
FROM deal_properties
JOIN activity on activity.deal_id = deal_properties.deal_id
AND activity.activity_status = 3
JOIN banks ON banks.deal_id = activity.deal_id
AND banks.is_rewarded = 1) a
on a.property_id = properties.property_id;

and that will get me all the closed properties, with the rewarded banks, but I cant seem to limit that by the max(activity_date).

Upvotes: 0

Views: 62

Answers (2)

DarbyM
DarbyM

Reputation: 1203

Option 1

The following gives what you're looking for following your current line of thought:

SELECT LastActivities.property_id, ActivityDetails.bank_id, LastActivities.activity_date 
FROM (
      SELECT p.property_id, MAX(a.activity_date) AS activity_date
      FROM properties p
      JOIN deal_properties dp
          ON dp.property_id = p.property_id
      JOIN activity a
          ON a.deal_id = dp.deal_id AND a.activity_status = 3
      GROUP BY p.property_id
) LastActivities

JOIN(
      SELECT a.activity_date, dp.property_id, b.bank_id 
      FROM deal_properties dp
      JOIN activity a
          ON a.deal_id = dp.deal_id AND a.activity_status = 3
      JOIN banks b
          ON b.deal_id = a.deal_id AND b.is_rewarded = 1
) ActivityDetails
  ON ActivityDetails.property_id = LastActivities.property_id 
  AND ActivityDetails.activity_date = LastActivities.activity_date

Here is the fiddle: HERE

Option 2

Below is another way to get the same results... This should be a bit more efficient as it only has one derived table instead of two.

SELECT p.property_id, b.bank_id, a.activity_date
FROM activity a
JOIN banks b
  ON b.deal_id = a.deal_id AND b.is_rewarded = 1
JOIN deal_properties dp
  ON dp.deal_id = a.deal_id
JOIN properties p
  ON p.property_id = dp.property_id
JOIN(SELECT p.property_id, max(a.activity_date) AS activity_date
    FROM activity a
    JOIN deal_properties dp
      ON dp.deal_id = a.deal_id
    JOIN properties p
      ON p.property_id = dp.property_id
     GROUP BY p.property_id
) latest
  ON latest.activity_date = a.activity_date AND latest.property_id = p.property_id

WHERE a.activity_status = 3

Here is the fiddle for option 2: HERE

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

looking to your sample seems you need 

Select * from properties p
inner join 
( SELECT deal_properties.property_id as property_id , max(activity.activity_date) max_date
  FROM deal_properties
  INNER JOIN activity on activity.deal_id = deal_properties.deal_id 
                   AND activity.activity_status = 3
  INNER JOIN banks ON banks.deal_id = activity.deal_id AND banks.is_rewarded = 1
  group by property_id
  ) a on  a.property_id = p.property_id;

Upvotes: 0

Related Questions