How do I maintain manager at level 2 when no employees (level 1) assigned?

Question

I have two tables to use for employee manager relationship. One table is employee id, and the other is the parent child link. I am joining the employee table multiple times along with the parent child link table, trying to create: Sr Mgr (Lvl 1), Mgr (Lvl 2), and Employee (Lvl 3) however I am running into an issue with Mgr who don't have employees assigned to them. There isnt a destination record with the mgrid as source.

How would I go about creating a null record and keeping the mgr at level 2 who doesnt have employeeid assigned to them?

Table Layout

EmpID table as Eid

EmpID
123
456

ParentChild Table as PC:

DestinationID (Child)	SourceID (Parent)
456	123
789	123
111	789

Joins

Select eid.empID (Level_3), pc.sourceid (level_2), pc2.sourceid (level_1)
   From empid as eid
    Left join parentchild as pc on eid.empID = pc.destinationID
     Left Join empid as eid2 on pc.sourceId = eid2.empid
      Left join parentchild as pc2 on eid2.empid = pc2.destinationID

Expected results:

Level_1	Level_2	Level_3
Null	456	123
111	789	123

The manager without employees only shows up at level 1 since they report to sr manager and not at level 2 with a level 1 null record.

Level_1	Level_2	Level_3
456	123	Null
111	789	123

Answer 1

With oracle (like Oracle 19c, etc) you'll need to make a few adjustments to your SQL syntax. We only needed to use the linkage table parentchild to obtain the detail you required.

Try this one:

SELECT pc2.destinationid level_1, pc.destinationid level_2, pc.sourceid Level_3
  FROM      parentchild   pc
  LEFT JOIN parentchild   pc2   on pc2.sourceid  = pc.destinationid
 WHERE pc.sourceid = 123
;

Result:

+------+-----------+
|    1 |   2 |   3 |
+------+-----------+
|  111 | 789 | 123 |
| null | 456 | 123 |
+------+-----------+

Full working test case

Note: With recursion, we don't need to use explicit joins for each level of the tree and the depth doesn't need to be known. Oracle has two ways to do that:

1. WITH clause - Common Table Expression
2. CONNECT BY - Oracle specific syntax