Mysql Join with max date and first row

Question

I have 2 tables properties and property_history structure some thing like that

proerties -
id | property_address | owner 
1  | abc              | xyz
2  | 123              | efg

property_history- 

id | property_id |   date1    |   date2    |   date3    |    date4
1  | 1           | 2012-05-01 | 0000-00-00 | 2002-06-11 | 2006-06-11 |
2  | 1           | 2019-05-01 | 0000-00-00 | 2003-06-11 | 2007-06-11 |
3  | 1           | 0000-00-00 | 2011-06-11 | 2004-06-11 | 2011-06-11 |
4  | 1           | 0000-00-00 | 2020-01-31 | 2005-06-11 | 0000-00-11 |

I need to join these two table as

All rows from properties table
Only 1 row form property history with latest date from any of the date column as latest date for eg. latest date between( date1, date2, date3, date4) as latest_date as one column
If property_history not have any row with respect property id then latest_date should be null

Use LEFT JOIN with a subquery that gets the latest row for each property ID. See https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 for how to write that subquery. — Barmar, May 28 '20 at 17:15
One hint aside from learning the left-join, you will probably want to use the "GREATEST" function for your dates... Greatest( ph.date1, ph.date2, ph.date3, ph.date4 ) as MostRecentDate the "ph" is just an short alias for the "property_history" table — DRapp, May 28 '20 at 18:45

score 0 · Answer 1 · answered May 28 '20 at 18:58

Please try this query

Select proerties.id,proerties.property_address,proerties.owner from proerties 
left join ( Select Case when date(date1)>=date(date2) and date(date1)>=date(date3) and date(date1)>=date(date4) then date1 when date(date2)>=date(date1) and date(date2)>=date(date3) and date(date2)>=date(date4) then date2  when date(date3)>=date(date1) and date(date3)>=date(date2) and date(date3)>=date(date4) then date3  when date(date4)>=date(date1) and date(date4)>=date(date2) and date(date4)>=date(date3) then date3 END AS max_date,property_id  from property_history) as update_history on update_history.property_id=proerties.id

And let me know if you are getting expected result or not.

I have not checked your solution, I made my query – rajanbhadauria May 29 '20 at 09:22 — rajanbhadauria, May 29 '20 at 09:22

score 0 · Answer 2 · answered May 29 '20 at 09:24

SELECT properties.id, properties.parcel_id, properties.state, properties.county, properties.assessed_value, properties.in_same_zip, properties.in_same_state
FROM  properties left join (SELECT GREATEST(tax_history.date_paid_2nd_half_2,
     COALESCE(tax_history.date_paid_2nd_half_1, 0),
     COALESCE(tax_history.date_paid_1st_half_2, 0),
     COALESCE(tax_history.date_paid_1st_half_1, 0)) as latest_date, property_id from tax_history) as tax_history ON tax_history.property_id = properties.id

This query is working but it is very slow, I have thousands of records

Mysql Join with max date and first row

2 Answers2