特定の行でjoinするSQLクエリの書き方について


2つのテーブルを特定の期間におけるレコードでjoinをしようとしています。  
テーブルの仕様は以下です。

table_A

event_code | reg_time | description  
---|---|---  
apple | 2022-08-01 00:00:00 | it's apple  
orange | 2022-08-01 00:00:00 | it's orange  
grape | 2022-08-01 00:00:00 | it's grape  
apple2 | 2022-09-01 00:00:00 | big apple  
orange | 2022-09-01 00:00:00 | big orage  
grape | 2022-09-01 00:00:00 | big grape  
  
table_B

event_code | event_time | user_id  
---|---|---  
apple | 2022-08-10 00:00:00 | 0001  
orange | 2022-08-21 00:00:00 | 0001  
grape | 2022-08-30 00:00:00 | 0001  
apple2 | 2022-09-11 00:00:00 | 0001  
orange | 2022-09-21 00:00:00 | 0001  
grape | 2022-09-31 00:00:00 | 0001  
  
join後の期待する結果は以下の通りです。  
SQLクエリで書けますでしょうか？  
joinするtable_A
をevent_codeで連結したいのですが、table_Bのevent_timeより過去日での最新日付を持ったevent_codeでjoinさせたいです。

event_code | event_time | user_id | reg_time | description  
---|---|---|---|---  
apple | 2022-08-10 00:00:00 | 0001 | 2022-08-01 00:00:00 | it's apple  
orange | 2022-08-21 00:00:00 | 0001 | 2022-08-01 00:00:00 | it's orange  
grape | 2022-08-30 00:00:00 | 0001 | 2022-08-01 00:00:00 | it's grape  
apple2 | 2022-09-11 00:00:00 | 0001 | 2022-09-01 00:00:00 | big apple  
orange | 2022-09-21 00:00:00 | 0001 | 2022-09-01 00:00:00 | big orage  
grape | 2022-09-31 00:00:00 | 0001 | 2022-09-01 00:00:00 | big grape




> joinするtable_A
> をevent_codeで連結したいのですが、table_Bのevent_timeより過去日での最新日付を持ったevent_codeでjoinさせたいです。

[View on DB Fiddle](https://www.db-fiddle.com/f/285FUJ2SMuTFFxW3K5D1MB/1)

[code]

    CREATE TABLE table_A 
        (event_code  VARCHAR(10),
         reg_time    DATETIME,
         description VARCHAR(15));
    
    INSERT INTO table_A
    VALUES
      ('apple',  '2022-08-01 00:00:00', 'it''s apple'),
      ('orange', '2022-08-01 00:00:00', 'it''s orange'),
      ('grape',  '2022-08-01 00:00:00', 'it''s grape'),
      ('apple2', '2022-09-01 00:00:00', 'big apple'),
      ('orange', '2022-09-01 00:00:00', 'big orage'),
      ('grape',  '2022-09-01 00:00:00', 'big grape');
    
    CREATE TABLE table_B
        (event_code VARCHAR(10),
         event_time DATETIME,
         user_id    VARCHAR(5));
    
    INSERT INTO table_B
    VALUES
      ('apple',  '2022-08-10 00:00:00', '0001'),
      ('orange', '2022-08-21 00:00:00', '0001'),
      ('grape',  '2022-08-30 00:00:00', '0001'),
      ('apple2', '2022-09-11 00:00:00', '0001'),
      ('orange', '2022-09-21 00:00:00', '0001'),
      ('grape',  '2022-09-30 00:00:00', '0001');
    
    
    SELECT table_B.event_code, event_time, user_id, reg_time, description
    FROM table_B INNER JOIN table_A
    ON table_B.event_code = table_A.event_code AND
       reg_time =
         (SELECT MAX(reg_time) FROM table_A WHERE reg_time < event_time);
    
[/code]

event_code | event_time | user_id | reg_time | description  
---|---|---|---|---  
apple | 2022-08-10 00:00:00 | 0001 | 2022-08-01 00:00:00 | it's apple  
orange | 2022-08-21 00:00:00 | 0001 | 2022-08-01 00:00:00 | it's orange  
grape | 2022-08-30 00:00:00 | 0001 | 2022-08-01 00:00:00 | it's grape  
apple2 | 2022-09-11 00:00:00 | 0001 | 2022-09-01 00:00:00 | big apple  
orange | 2022-09-21 00:00:00 | 0001 | 2022-09-01 00:00:00 | big orage  
grape | 2022-09-30 00:00:00 | 0001 | 2022-09-01 00:00:00 | big grape




> joinするtable_A
> をevent_codeで連結したいのですが、table_Bのevent_timeより過去日での最新日付を持ったevent_codeでjoinさせたいです。

[View on DB Fiddle](https://www.db-fiddle.com/f/285FUJ2SMuTFFxW3K5D1MB/1)

[code]

    CREATE TABLE table_A 
        (event_code  VARCHAR(10),
         reg_time    DATETIME,
         description VARCHAR(15));
    
    INSERT INTO table_A
    VALUES
      ('apple',  '2022-08-01 00:00:00', 'it''s apple'),
      ('orange', '2022-08-01 00:00:00', 'it''s orange'),
      ('grape',  '2022-08-01 00:00:00', 'it''s grape'),
      ('apple2', '2022-09-01 00:00:00', 'big apple'),
      ('orange', '2022-09-01 00:00:00', 'big orage'),
      ('grape',  '2022-09-01 00:00:00', 'big grape');
    
    CREATE TABLE table_B
        (event_code VARCHAR(10),
         event_time DATETIME,
         user_id    VARCHAR(5));
    
    INSERT INTO table_B
    VALUES
      ('apple',  '2022-08-10 00:00:00', '0001'),
      ('orange', '2022-08-21 00:00:00', '0001'),
      ('grape',  '2022-08-30 00:00:00', '0001'),
      ('apple2', '2022-09-11 00:00:00', '0001'),
      ('orange', '2022-09-21 00:00:00', '0001'),
      ('grape',  '2022-09-30 00:00:00', '0001');
    
    
    SELECT table_B.event_code, event_time, user_id, reg_time, description
    FROM table_B INNER JOIN table_A
    ON table_B.event_code = table_A.event_code AND
       reg_time =
         (SELECT MAX(reg_time) FROM table_A WHERE reg_time < event_time);
    
[/code]

event_code | event_time | user_id | reg_time | description  
---|---|---|---|---  
apple | 2022-08-10 00:00:00 | 0001 | 2022-08-01 00:00:00 | it's apple  
orange | 2022-08-21 00:00:00 | 0001 | 2022-08-01 00:00:00 | it's orange  
grape | 2022-08-30 00:00:00 | 0001 | 2022-08-01 00:00:00 | it's grape  
apple2 | 2022-09-11 00:00:00 | 0001 | 2022-09-01 00:00:00 | big apple  
orange | 2022-09-21 00:00:00 | 0001 | 2022-09-01 00:00:00 | big orage  
grape | 2022-09-30 00:00:00 | 0001 | 2022-09-01 00:00:00 | big grape

