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.
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’)
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.
Firebase Cloud Functions: PubSub, "res.on is not a function"
TypeError: Cannot read properties of undefined (reading 'createMessageComponentCollector')
Anybody tell how to use auto populated states when i select particular country from dropdown for wordpress plateform
I am using ASPNet Core 2 and Entity Framework Core with MySQL
I'd like to remove MySQL data from each table in a database on MySQL 56