0

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).

2
  • 1
    first you need to tell us, which mysql version you are using and second what happens if there are twio entries for the 15th and one is good and one is bad which to choose
    – nbk
    Mar 17 at 22:41
  • It's not mysql I got geopackage file (qgis app) and many tables inside, but i can make new tables with sql queries. Geopackage is like sqlite 3
    – Pankrat
    Mar 18 at 20:41
0

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.

8
  • Thanks! Work perfectly! The same date it's not a problem because I can add only one survey or measurement per day, so your solution is very good form me.
    – Pankrat
    Mar 18 at 21:17
  • @Pankrat Great! No problem! 🙂
    – J.D.
    Mar 18 at 22:41
  • Sorry but i edited my post, I did not want to start a new topic, the table has changed a bit, could you have a look at it now?
    – Pankrat
    Mar 24 at 21:03
  • 1
    Sorry for that, i'm newbie. Thank you for the quick reply, I will check as soon as I run the project.
    – Pankrat
    Mar 24 at 21:19
  • 1
    I don't know how you did it but it works! Thank you very much. Now I can expand my table more :)
    – Pankrat
    Mar 24 at 21:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.