By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create table Users (
Username varchar2(10),
Password1 int
)
Create Table UsersLoginAttempts (
Username varchar2(10),
Password1 int,
AttemptDate date
)
insert into Users (username, password1)
SELECT ' MAllen', 123 FROM DUAL UNION ALL
SELECT 'SEllis ', 124 FROM DUAL UNION ALL
SELECT 'MChen ', 126 FROM DUAL;
3 rows affected
insert into UsersLoginAttempts (username, password1, attemptdate)
SELECT 'MAllen ', 123, DATE '2022-10-01' FROM DUAL UNION ALL
SELECT ' SEllis ', 124, DATE '2022-10-01' FROM DUAL UNION ALL
SELECT ' MChen ', 126, DATE '2022-10-01' FROM DUAL UNION ALL
SELECT 'MAllen ', 126, DATE '2022-10-08' FROM DUAL UNION ALL
SELECT ' SEllis ', 123, DATE '2022-10-08' FROM DUAL UNION ALL
SELECT ' MChen', 128, DATE '2022-10-08' FROM DUAL;
6 rows affected
Select t.*,
u.*
from Users u
Outer apply (
select *
from UsersLoginAttempts ula
where TRIM(ula.UserName) = TRIM(u.UserName)
order by ula.AttemptDate desc
FETCH FIRST ROW ONLY
) t
| USERNAME | PASSWORD1 | ATTEMPTDATE | USERNAME | PASSWORD1 |
|---|---|---|---|---|
| MAllen | 126 | 08-OCT-22 | MAllen | 123 |
| SEllis | 123 | 08-OCT-22 | SEllis | 124 |
| MChen | 128 | 08-OCT-22 | MChen | 126 |