CODEWITHSUNDEEP
mysql
rajanbhadauria
rajanbhadauria

Reputation: 435

Mysql Join with max date and first row

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

  1. All rows from properties table
  2. 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
  3. If property_history not have any row with respect property id then latest_date should be null

Upvotes: 0

Views: 137

Answers (2)

rajanbhadauria
rajanbhadauria

Reputation: 435

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

Upvotes: 0

Biki mallik
Biki mallik

Reputation: 152

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.

Upvotes: 0

Related Questions