Table 1: department of a relational table
Father_code child_code
2 5
2 6
3 7
3 8
4 and 9
4 to 10


Table 2: department details table

FATHER_CODE NAME
Unit 2
3 department of 3
4 department of 4
5 families 1
6 section 2
7 families and 1
8 section 2
9 families 1
10 section 2

Table 3:

Number department number department number
Unit 2
5 department 2 division 1
6 department 2 families, 2
3 department of 3
7 department 3 division 1
8 unit 3 section 2
Department of 10 4 families 2
4 department of 4
9 department of 4 families 1


-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
According to table 1 and table 2, table 3 the results (all fields are varchar2 type)


Reference answer:
SELECT
C. no.
, c. department number
, c. department number
The FROM (
SELECT

B. ATHER_CODE number
The CASE WHEN b. ATHER_CODE IN (SELECT FATHER_CODE FROM DEPT_A)
THEN B.N AME
The ELSE (SELECT
C.N AME
The FROM DEPT_B C WHERE C.F ATHER_CODE=
(SELECT A.F ATHER_CODE FROM DEPT_A WHERE A A.C HILD_CODE=b. ATHER_CODE))
END department number
The CASE WHEN b. ATHER_CODE IN (SELECT FATHER_CODE FROM DEPT_A)
THEN a NULL
The ELSE B.N AME
END department number
The FROM DEPT_B B
C)
The ORDER BY c. department number, c. Numbers;