SQL, get foreign key value. Foreign key references the same table

Question

MS SQL Server database.

I have this simple structure: (sorry about primary keys not in a right places)

In TEACHER table I have a foreign key "Chief", which references TEACHER.ID (same table). How can I get not an integer(Teacher.ID), but a Name of a Chief(TEACHER.Name for Chief), while doing SELECT query?

This one gets just an integer(ID of a Chief):

SELECT DEPARTMENT.Name, TEACHER.Name, TEACHER.IDCode, POST.Name, TEACHER.Tel, TEACHER.Salary, TEACHER.Rise, TEACHER.HireDate, Chief
FROM TEACHER, DEPARTMENT, POST
WHERE TEACHER.ID_Post = POST.ID AND
    TEACHER.ID_Department = DEPARTMENT.ID;
GO

Answer 1

JOIN the TEACHER table one more time, like so:

SELECT 
  d.Name, 
  t.Name, 
  t.IDCode, 
  p.Name, 
  t.Tel, 
  t.Salary, 
  t.Rise, 
  t.HireDate, 
  chief.Name 'Chief Name'
FROM TEACHER t 
INNER JOIN TEACHER chief ON t.Chief = chief.ID
INNER JOIN DEPARTMENT d ON t.ID_Department = d.ID
INNER JOIN POST p ON t.ID_Post = p.ID;

And use the ANS-SQL-92 JOIN syntax instead of the old syntax that you are using in your query. They are the same, but this is the recommended syntax.