Foreign Key Refers the Primary Key Columns of Same Table

I have two tables like this:

Table 1 – CivilOffices

Id - Primary key
OfficeId - Foreign Key - refer to Office Table
Created Date
Location 
ParentOffice - Foreign Key - refer to Office Table

Table 2 – Offices

OfficeId - Primary key
Name

I want to query all the offices with their respective parent office name. But when I tried to query this, the query returns the parent office id instead of parent office name.

Here is the query I used.

SELECT O.OfficeId, O.Name, CO.ParentOffice
FROM Offices As O
LEFT JOIN CivilOffices As CO
ON O.OfficeId=CO.OfficeId;

Here is the result

OfficeId | Name    | ParentOffice
---------------------------------
1        |Office1  |NULL
2        |Office2  |NULL
3        |Office3  |1
4        |Office4  |3
5        |Office5  |NULL
6        |Office6  |2

Instead, I want result something like this

---------------------------------
OfficeId | Name    | ParentOffice
---------------------------------
1        |Office1  |NULL
2        |Office2  |NULL
3        |Office3  |Office1
4        |Office4  |Office3
5        |Office5  |NULL
6        |Office6  |Office2

Answer

I would approach this as follows:

SELECT
    co1.OfficeId,
    o1.Name,
    o2.Name AS ParentOffice
FROM Offices o1
LEFT JOIN CivilOffices co1
    ON o1.OfficeId = co1.OfficeId
LEFT JOIN Offices o2
    ON co1.ParentOffice = o2.OfficeId;

We do a self-join on the CivilOffices table to match each record with a potential parent. Then, we use two separate joins to Offices to bring in the office name as well as possibly the parent office name, should the latter exist.