MySQL - Find closest value from another table-CodePudding

Having trouble resolving this problem. I have a table with many rows and columns, I'm trying to find the closest value from another but it gives me different result. I want to populate the values of table2 to table1. Please find below sample data and expected result.

I have query like this.

select  t1.idno, t1.startdate,  
    (
    SELECT  t2.createddate
    FROM    tmp.Table2  t2
    WHERE   t1.idno = t2.idno
            and t1.startdate <= t2.createddate
    ORDER BY DATEDIFF(  t1.startdate,  t2.createddate) LIMIT 1
    ) AS createddate

from tmp.Table1 t1

result from the query

 -------- ---------------------- ------------------- 
|idno    |    startdate         | createddate       |
 -------- ---------------------- ------------------- 
|12345340|  2021-07-13 23:45:59 |2022-07-19 15:25:01|
|12345340|  2022-02-22 10:07:41 |2022-07-19 15:25:01|
|12345340|  2022-06-17 06:20:20 |2022-07-19 15:25:01|
|12345341|  2020-12-08 17:32:51 |2022-05-30 22:45:07|
|12345341|  2021-09-07 14:35:34 |2022-05-30 22:45:07|
 -------- ---------------------- -------------------

Table1

 ----------------- ------------------- 
|    idno         |     startdate     |
 ----------------- -------------------  
| 12345340        |2021-07-13 23:45:59|
| 12345340        |2022-02-22 10:07:41|
| 12345340        |2022-06-17 06:20:20|
| 12345341        |2020-12-08 17:32:51|
| 12345341        |2021-09-07 14:35:34|
 ----------------- -------------------

Table2

 ----------------- -------------------- ----------- 
|    idno         |     createddate    | status    |
 ----------------- -------------------- ----------- 
| 12345340        |2022-07-19 15:25:01 |   1       |
| 12345341        |2022-05-30 22:45:07 |   0       | 
 ----------------- -------------------- -----------

Expected result

 ----------------- ------------------- -------------------- ------- 
|    idno         |     startdate     |     createddate    | status|
 ----------------- ------------------- -------------------- -------|
| 12345340        |2022-06-17 06:20:20| 2022-07-19 15:25:01|  1    |
| 12345341        |2021-09-07 14:35:34| 2022-05-30 22:45:07|  0    |
 ----------------- ------------------- ----------------------------

 ----------------- ------------------- -------------------- ------- 
|    idno         |     startdate     |   createddate      | status|
 ----------------- ------------------- -------------------- ------- 
| 12345340        |2021-07-13 23:45:59|                    |       |
| 12345340        |2022-02-22 10:07:41|                    |       |
| 12345340        |2022-06-17 06:20:20|2022-07-19 15:25:01 |   1   |
| 12345341        |2020-12-08 17:32:51|                    |       |
| 12345341        |2021-09-07 14:35:34|2022-05-30 22:45:07 |   0   |
 ----------------- ------------------- -------------------- -------

Thanks

CodePudding user response：

I'd suggest

SELECT t1.idno, 
       min(t1.startdate), 
       t2.creationdate
  FROM t1
  JOIN tmp.Table2 t2
    ON t1.idno = t2.idno
   AND t1.startdate <= t2.startdate
 GROUP BY t1.idno, t2.creationdate;

CodePudding user response：

Hello Please test this:

select  t1.idno, MAX(t1.startdate) AS startdate,MAX(
    (
    SELECT  t2.createddate
    FROM    Table2  t2
    WHERE   t1.idno = t2.idno
            and t1.startdate <= t2.createddate
    ORDER BY DATEDIFF(  t1.startdate,  t2.createddate) LIMIT 1
    )) AS createddate, t2.status
FROM Table1 as t1 JOIN Table2 as t2 ON t1.idno = t2.idno
GROUP BY t1.idno,t2.status;

If we test the result: