I've got a query that I want to use as a subquery, but I'm getting weird behavior. The subquery in question is:

SELECT
lpad(Person_Manager_Person_ID,level*5) AS Person_ID,
Level AS H_Level
FROM
PERSON_MANAGER
START WITH
Person_Manager_Manager_ID IS NULL
CONNECT BY
PRIOR Person_Manager_Person_ID = Person_Manager_Manager_ID

This works as expected (returning a hierarchy of employees starting at the top). However, if I try and use it as a subquery, the first person is not returned, even though the first person is present in both tables. Why does this happen?


EX:

This query output:
1 | 1
2 | 2
3 | 3
4 | 2
5 | 3

Second Query:
SELECT
Person_ID, H_Level, Level, First_Name, Last_Name
FROM
Person,
(
SELECT
lpad(Person_Manager_Person_ID,level*5) AS Person_ID,
Level AS H_Level
FROM
PERSON_MANAGER
START WITH
Person_Manager_Manager_ID IS NULL
CONNECT BY
PRIOR Person_Manager_Person_ID = Person_Manager_Manager_ID
)
WHERE Person.P_ID = Person_ID

Output:
2 | 2 | First1 | Last1
3 | 3 | First2 | Last2
4 | 2 | First3 | Last3
5 | 3 | First4 | Last4