How do I join data from two result of SQL oracle?

Question

I have to SQL oracle scripts and I would like to join data from two result of SQL oracle.

First script:

select USERNAME,"CLIENT NAME","VERSION",TASK,"LOGIN TIME" from (select sessions.USERNAME, sessions.clientname as "CLIENT NAME", sessions.version as "VERSION", users.TASK, sessions.LOGINTIME as "LOGIN TIME", ROW_NUMBER() OVER (partition by sessions.UserName, sessions.ClientName ORDER BY sessions.LoginTime DESC) as Rn
FROM users
RIGHT JOIN sessions on users.USERNAME = sessions.USERNAME WHERE sessions.USERNAME = 'TEST_PROD' AND sessions.LOGINTIME >= to_date('15-FEB-22','DD-MON-YY')) X WHERE Rn = 1;

Result:

Data from table "usergroups":

Second script:
select usergroups.group, usergroups.groupname, users.USERNAME
from users
left join usergroups.group on users.groupname = usergroups.groupname;

Question: How do I join from two result of SQL oracle?
My expectation is:

Answer 1

select ug.GROUP, ug.GROUPNAME, y.*
from
(select USERNAME,"CLIENT NAME","VERSION",TASK,"LOGIN TIME" from (select users.groupName, sessions.USERNAME, sessions.clientname as "CLIENT NAME", sessions.version as "VERSION", users.TASK, sessions.LOGINTIME as "LOGIN TIME", ROW_NUMBER() OVER (partition by sessions.UserName, sessions.ClientName ORDER BY sessions.LoginTime DESC) as Rn
FROM users
RIGHT JOIN sessions on users.USERNAME = sessions.USERNAME WHERE sessions.USERNAME = 'TEST_PROD' AND sessions.LOGINTIME >= to_date('15-FEB-22','DD-MON-YY')) X WHERE Rn = 1) y  
INNER JOIN UserGroups ug on y.GroupName = ug.GroupName
 ;

Answer 2

Hi @Smile_28
You can have a try on multiple CTEs, like this :

;WITH CTE1 AS  
(  
 --Put your First script here  
),CTE2 AS  
(  
 --Put your Second script here  
)  
SELECT <Column_List>  
FROM CTE1 JOIN CTE2 ON <......>  

Best regards,
LiHong

Answer 3

This is a Microsoft SQL Server forum. For help with Oracle, I suggest you post your questions on https://community.oracle.com/hub/