I have a table which is called TASK_CHECK and it looks like this:

There is another table which is called TASK_SCHEDULE, which looks like this:

Now I need to display first table but with one more column, I need to display the first next check_id from second table which happens after completion date. For example:

CodePudding user response：

Here's one option (sample data in lines #1 - 12; you don't need that - query you might be interested in begins at line #14):

SQL> with
  2  task_check (type, completion_date) as
  3    (select 'KDA', date '2021-12-17' from dual union all
  4     select 'KDB', date '2021-12-18' from dual
  5    ),
  6  task_schedule (check_id, type, scheduled_date) as
  7    (select 201, 'KDA', date '2021-12-19' from dual union all
  8     select 202, 'KDA', date '2021-12-22' from dual union all
  9     select 203, 'KDA', date '2021-12-23' from dual union all
 10     select 204, 'KDB', date '2021-12-21' from dual union all
 11     select 205, 'KDB', date '2021-12-23' from dual
 12    )
 13  --
 14  select c.type, c.completion_date, s.scheduled_date, s.check_id
 15  from task_check c join task_schedule s on s.type = c.type
 16  where s.scheduled_date = (select min(s1.scheduled_date)
 17                            from task_schedule s1
 18                            where s1.type = s.type
 19                              and s1.scheduled_date > c.completion_date
 20                           );

TYP COMPLETION SCHEDULED_   CHECK_ID
--- ---------- ---------- ----------
KDA 17.12.2021 19.12.2021        201
KDB 18.12.2021 21.12.2021        204

SQL>

CodePudding user response：

You can CROSS JOIN a lateral derived table:

select c.type, c.completion_date, s.scheduled_date, s.check_id
from task_check c
  cross join lateral (select * from task_schedule
                      where type = c.type
                        and scheduled_date > c.completion_date
                      order by scheduled_date
                      fetch first 1 row only) s