SQLite compare two tables and fetch the actual row

Question

I have two SQLite tables with same fields:

The first table named as FB_Config_Trace and the second FB_Settings.

FB_Config_Trace will be filled by the script to fetch the latest values from an configuration file, FB_Settings will be set by an user.

Sample Data:

FB_Config_Trace:

FB_Settings:

How i can fetch the latest rows/entries by given Time?

Answer 1

Join FB_Settings to FB_Config_Trace with a LEFT join like this:

SELECT COALESCE(c.ID, s.ID) ID,
       s.Key,
       CASE WHEN c.ID IS NULL THEN s.Value ELSE c.Value END Value,
       COALESCE(c.Time, s.Time) Time
FROM FB_Settings s LEFT JOIN FB_Config_Trace c 
ON c.Key = s.Key AND c.Time > s.Time