MySQL Join Three Tables – Query with Max Date Value

join;MySQLqueryselectsqlite

I'm trying join three tables:

hydrants
-----------------
fid | h_number | 
-----------------
 1  | 2525     |
-----------------

hydrats_survey
----------------------------------------------
fid | survey_date | condition | hydrants_fid|
----------------------------------------------
  1 |  2020-02-12 | good      | 1           |
----------------------------------------------
  2 |  2020-02-15 | good      | 1           |
----------------------------------------------

hydrants_measurement
----------------------------------------------------
fid | measurement_date | condition | hydrants_fid |
----------------------------------------------------
 1  | 2020-02-01       | bad       | 1            |
----------------------------------------------------
 2  | 2020-02-05       | good      | 1            |
----------------------------------------------------

I'm trying create one table, where hydrants got only one date and one condition but i need only last (max) date from hydrats_survey or hydrants_measurement. Like this:

 hydrants_join_table
----------------------------------------------
 fid | h_number | last_date | condition | 
----------------------------------------------
  1  |  2525    |  2020-02-15 | good      |   <---- it's last date from hydrats_survey and hydrants_measurement where hydrant_fid=1
----------------------------------------------

Edit:
this third table was changed a little:

hydrants_measurement

-----------------------------------------------------------------------------
fid | measurement_date | condition | st_pressure | dyn_pressure |hydrants_fid |
-----------------------------------------------------------------------------
 1  | 2020-02-01       | bad       | 10          | 0,5          |   1         |
-----------------------------------------------------------------------------
 2  | 2020-02-05       | good      | 15          | 0,8          |   1         |
-----------------------------------------------------------------------------

it's almost the same situation, tries to create a table where the last measurement or survey will be, but if it is a measurement, additional fields should appear (st_pressure and dyn_pressure).

Best Answer

You can use the UNION operator to combine your hydrats_survey and hydrants_measurement tables as one dataset, then you can use the ROW_NUMBER() window function to get the latest row per hydrant from that dataset with a query like the following:

SELECT hydrants.fid, hydrants.h_number, conditions.hydrant_condition_date as last_date, conditions.condition, conditions.st_pressure, conditions.dyn_pressure
FROM hydrants
INNER JOIN 
(
    SELECT fid, hydrant_condition_date, condition, hydrants_fid, st_pressure, dyn_pressure, ROW_NUMBER() OVER (PARTITION BY hydrants_fid ORDER BY hydrant_condition_date DESC) AS SortId
    FROM 
    (
        SELECT fid, survey_date AS hydrant_condition_date, condition, hydrants_fid, CAST(NULL AS INT) AS st_pressure, CAST(NULL AS DECIMAL(10,2)) AS dyn_pressure
        FROM hydrats_survey

        UNION

        SELECT fid, measurement_date AS hydrant_condition_date, condition, hydrants_fid, st_pressure, dyn_pressure
        FROM hydrants_measurement
    )
) conditions
    ON hydrants.fid = conditions.hydrants_id
WHERE conditions.SortId = 1 -- Filters out everything but the latest record per hydrant ordered by date

The only caveat is if you have two records with the same date that are different in other ways (such as different conditions, as nbk points out), this will choose one of those two at random because there is no distinct field combination to order on inside of the ROW_NUMBER() function.

One might try to solve this by using the fid column of the hydrats_survey and hydrants_measurement tables, but those two fields are independent of each other and could still end up creating a non-unique dataset when unioned together in the subquery. So you'd need to decide on a unique field to be the tie-breaker (e.g. if there's a bad and good condition on the same day, then take the record with the bad condition) or accept the results will be semi-random when there are two records on the same date.

Related Question