O r a c l e
The with clause is here just to generate some sample data and, as such, it is not a part of the answer.
After joining the tables you can use LAST_VALUE analytic function with windowing clause to get the next PARENT_ID of the user. That column (PARENT_ID_2) contains a value only within the first row of a particular USER_ID (ROW_NUMBER analytic function). Afterwords just filter out rows where PARENT_ID_2 is empty...
Sample data:
WITH
tbl_1 AS
(
Select 1 "USER_ID", 'Dave' "USER_NAME", 1 "PARENT_ID" From Dual Union All
Select 1 "USER_ID", 'Dave' "USER_NAME", 2 "PARENT_ID" From Dual Union All
Select 2 "USER_ID", 'Sally' "USER_NAME", 3 "PARENT_ID" From Dual Union All
Select 2 "USER_ID", 'Sally' "USER_NAME", 4 "PARENT_ID" From Dual
),
tbl_2 AS
(
Select 1 "PARENT_ID", 'Sarah' "USER_NAME" From Dual Union All
Select 2 "PARENT_ID", 'Joe' "USER_NAME" From Dual Union All
Select 3 "PARENT_ID", 'Tom' "USER_NAME" From Dual Union All
Select 4 "PARENT_ID", 'Mark' "USER_NAME" From Dual
)
Main SQL:
SELECT
*
FROM (
SELECT
t1.USER_ID "USER_ID",
t1.USER_NAME "USER_NAME",
t1.PARENT_ID "PARENT_ID_1",
CASE
WHEN ROW_NUMBER() OVER(PARTITION BY t1.USER_ID ORDER BY t1.USER_ID) = 1
THEN LAST_VALUE(t1.PARENT_ID) OVER(PARTITION BY t1.USER_ID ORDER BY t1.USER_ID ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
END "PARENT_ID_2"
FROM
tbl_1 t1
INNER JOIN
tbl_2 t2 ON(t1.PARENT_ID = t2.PARENT_ID)
)
WHERE PARENT_ID_2 Is Not Null
... and the Result ...
-- USER_ID USER_NAME PARENT_ID_1 PARENT_ID_2
-- ---------- --------- ----------- -----------
-- 1 Dave 1 2
-- 2 Sally 3 4
The windowing clause in this answer
ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING
takes curent and next row and returns the value defined by the analytic function (LAST_VALUE) taking care of grouping (PARTITION BY) and ordering of the rows. Regards...