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.

Rent Charter Buses Company
READ ALSO
How to make countary with associated state dropdown in wordpress contact form

How to make countary with associated state dropdown in wordpress contact form

Anybody tell how to use auto populated states when i select particular country from dropdown for wordpress plateform

187
ASP.Net Core - EntityFrameworkCore data is not adding, updating instead

ASP.Net Core - EntityFrameworkCore data is not adding, updating instead

I am using ASPNet Core 2 and Entity Framework Core with MySQL

281
Mysql remove data from table in a procedure

Mysql remove data from table in a procedure

I'd like to remove MySQL data from each table in a database on MySQL 56

226
WordPress posts with featured images and categories

WordPress posts with featured images and categories

I am looking for a solution for:

163