Return the most recent value when joining two tables

178

January 16, 2018, at 3:48 PM

I am trying to join two tables and return the most recent value for a field.

Currently, if aa.time_day does not equal bb.time, then the bb.time field returns null. I would like this to return the most recent value less than or equal to the aa.time_date value.

My query currently looks like this:

Select
  aa.day_time
  aa.name
  aa.value
  bb.name
  bb.target_value
  bb.time
FROM
x.table1 aa LEFT JOIN y.table2 bb
ON  aa.name = bb.name  AND aa.day_time=bb.time
WHERE aa.day_time = TO_DATE(‘01/01/2017’,’DD/MM/YYYY’)

Searching Stackoverflow and other websites showed me a number of solutions, unfortunately nothing worked. The query below is the closest I got to success as it did not throw up an error message, however it ran for several hours and I had to stop it. The query above worked in about 5 seconds.

Select
  aa.day_time
  aa.name
  aa.value
  bb.name
  bb.target_value
  bb.time
FROM
x.table1 aa LEFT JOIN y.table2 bb
ON  aa.name = bb.name  AND aa.day_time=
  (SELECT MAX (bb.time)
    FROM y.table2
    WHERE  bb.time <= aa.day_time)
WHERE a.day_time = TO_DATE(‘01/01/2017’,’DD/MM/YYYY’)

I'm not familiar with SQL, so thank you very much for your help in advance.

Answer 1

This is standard behaviour of LEFT JOIN.

If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table (https://dev.mysql.com/doc/refman/5.7/en/join.html)

In this case a NULL bb.time means there is no matching row in table2 given the condition aa.name = bb.name AND aa.day_time=bb.time.

You can create a select field with a value of aa.day_time or bb.time whichever is later using MySQL IF()

Select
  IF(bb.time IS NOT NULL and bb.time > aa.day_time, bb.time, aa.day_time) AS selected_time,
  aa.name,
  aa.value,
  bb.name,
  bb.target_value,
FROM
x.table1 aa LEFT JOIN y.table2 bb
ON  aa.name = bb.name  AND aa.day_time=bb.time
WHERE aa.day_time = TO_DATE(‘01/01/2017’,’DD/MM/YYYY’)

Answer 2

If this is oracle and there is a one to many relationship t1 to t2 then a using a cte to find the most recent date from t2 might do it

DROP TABLE T1;
DROP TABLE T2;
CREATE TABLE T1(DAY_TIME DATE,NAME VARCHAR(3), VALUE NUMBER);
CREATE TABLE T2(DAY_TIME DATE,NAME VARCHAR(3), VALUE NUMBER);
TRUNCATE TABLE T1;
INSERT INTO T1(day_time,NAME,VALUE) VALUES (to_date('2018-01-01','YYYY-MM-DD'),'aaa',10);
TRUNCATE TABLE T2;
INSERT INTO T2(day_time,NAME,VALUE) VALUES (to_date('2017-01-01','YYYY-MM-DD'),'aaa',10);
INSERT INTO T2(day_time,NAME,VALUE) VALUES (to_date('2018-02-01','YYYY-MM-DD'),'aaa',10);
SELECT * FROM T1;
SELECT * FROM T2;
WITH cte AS
(
select name,day_time,value 
from T2
where T2.day_time = (select MAX(t3.DAY_TIME) FROM T2 t3 WHERE t3.DAY_TIME <= TO_DATE('2018-01-01','YYYY-MM-DD') and t3.name = t2.name)
)
SELECT t1.name,t1.day_time,t1.value,
       cte.name,cte.day_time,cte.value
from t1
left join cte on t1.name = cte.name 
where t1.day_time = to_date('2018-01-01','YYYY-MM-DD');
NAME DAY_TIME                    VALUE NAME DAY_TIME                    VALUE
---- ---------------------- ---------- ---- ---------------------- ----------
aaa  01-JAN-2018 00:00:00           10 aaa  01-JAN-2017 00:00:00           10

If there are no entries at all in t2 then the t2 side of the select will by empty.