By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table users (id, name) as
select 1, 'User1' from dual
union all select 2, 'User2' from dual
union all select 3, 'User3' from dual;
3 rows affected
create table direcctions (id, direcction, xxx) as
select 1, 'Fail Street', '34' from dual
union all select 2, 'Swimming Street', '45' from dual
union all select 3, 'Fake Street', '01' from dual;
3 rows affected
create table jobs (id, job, id_user, id_direcction) as
select 1, 'Football Match', 1, 1 from dual
union all select 2, 'Bascketball Match', 3, 1 from dual
union all select 3, 'Swimming Race', 2, 2 from dual
union all select 4, 'Handball Game', 3, 2 from dual
union all select 5, 'Tennis Match', 2, 3 from dual;
5 rows affected
select id, job, id_user, id_direcction,
lag(id_user) over (partition by id_direcction order by id) as last_id_user
from jobs
order by id;
| ID | JOB | ID_USER | ID_DIRECCTION | LAST_ID_USER |
|---|---|---|---|---|
| 1 | Football Match | 1 | 1 | null |
| 2 | Bascketball Match | 3 | 1 | 1 |
| 3 | Swimming Race | 2 | 2 | null |
| 4 | Handball Game | 3 | 2 | 2 |
| 5 | Tennis Match | 2 | 3 | null |
select j.id, j.job, u.name, j.id_direcction, lu.name as last_name
from (
select id, job, id_user, id_direcction,
lag(id_user) over (partition by id_direcction order by id) as last_id_user
from jobs
) j
join users u on u.id = j.id_user
left join users lu on lu.id = j.last_id_user
order by j.id;
| ID | JOB | NAME | ID_DIRECCTION | LAST_NAME |
|---|---|---|---|---|
| 1 | Football Match | User1 | 1 | null |
| 2 | Bascketball Match | User3 | 1 | User1 |
| 3 | Swimming Race | User2 | 2 | null |
| 4 | Handball Game | User3 | 2 | User2 |
| 5 | Tennis Match | User2 | 3 | null |
select j.id, j.job, j.id_user, u.name, j.id_direcction, d.direcction,
j.last_id_user, lu.name as last_name
from (
select id, job, id_user, id_direcction,
lag(id_user) over (partition by id_direcction order by id) as last_id_user
from jobs
) j
join direcctions d on d.id = j.id_direcction
join users u on u.id = j.id_user
left join users lu on lu.id = j.last_id_user
order by j.id;
| ID | JOB | ID_USER | NAME | ID_DIRECCTION | DIRECCTION | LAST_ID_USER | LAST_NAME |
|---|---|---|---|---|---|---|---|
| 1 | Football Match | 1 | User1 | 1 | Fail Street | null | null |
| 2 | Bascketball Match | 3 | User3 | 1 | Fail Street | 1 | User1 |
| 3 | Swimming Race | 2 | User2 | 2 | Swimming Street | null | null |
| 4 | Handball Game | 3 | User3 | 2 | Swimming Street | 2 | User2 |
| 5 | Tennis Match | 2 | User2 | 3 | Fake Street | null | null |