How to query two tables and get the rows from one mask the ones from the other?

Question

WHAT I HAVE

I have two tables with the same structure. We can say that one is the main table, while the other is a side table, meaning that each row tracks an edit to a corresponding row from the main one. In order to match the edit to the original row I obviously use some keys.

WHAT I WANT TO DO

I need a query that returns a result set where the edit rows replace the originals.

For example I have this row in the side table:

which matches, by the person ID and the date which are KEYs, the highlighted row in the main table (below)

The query should return a set of four tuples where the row 96368 is replaced with the values from the edit row.

What kind of approach do you suggest? At the beginning I've taken into consideration an UNION, since the two tables share the same structure, but I don't know how to make the edits to mask the original rows.

Answer 1

SELECT CASE WHEN t2.data IS NULL THEN t1.id ELSE t2.id END id,
       idpersona, 
       data,
       CASE WHEN t2.data IS NULL THEN t1.settimana ELSE t2.settimana END settimana,
       CASE WHEN t2.data IS NULL THEN t1.idlegenda ELSE t2.idlegenda END idlegenda,
       CASE WHEN t2.data IS NULL THEN t1.creatoda ELSE t2.creatoda END creatoda,
       CASE WHEN t2.data IS NULL THEN t1.creatoil ELSE t2.creatoil END creatoil
FROM maintable t1
LEFT JOIN sidetable t2 USING (idpersona, data)

If matched row in sidetable table exists then t2.data (and t2.idpersona - you may use it instead freely) is not NULL and according output column returns the value from sidetable, otherwise t2.column is NULL and according output column returns the value from maintable.

Answer 2

Maybe I've found a solution.

The idea is to build an UNION without some rows from the main table.

Those rows are identified with an INNER JOIN matching dates and person IDs.

SELECT 
    id,
    idpersona,
    data,
    settimana,
    idlegenda,
    creatoda,
    creatoil
FROM
    arp.turni
WHERE
    idpersona = 10042
        AND YEAR(data) = 2024
        AND MONTH(data) = 1
        AND id != (SELECT 
            turni.id
        FROM
            turni
                JOIN
            turni_layer_contabilita ON turni.idpersona = turni_layer_contabilita.idpersona
                AND turni.data = turni_layer_contabilita.data
        WHERE
            MONTH(turni.data) = 1) 
UNION SELECT 
    *
FROM
    arp.turni_layer_contabilita
WHERE
    idpersona = 10042
        AND YEAR(data) = 2024
        AND MONTH(data) = 1;

It seems it works, but I'm open to improvements, if any.

Thanks in advance