title: SQL, get foreign key value. Foreign key references the same table
body: <p>MS SQL Server database.</p>
  
  <p>I have this simple structure: (sorry about primary keys not in a right places)</p>
  
  <p><img src="https://i.stack.imgur.com/c8TbC.png" alt="enter image description here"></p>
  
  <p>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?</p>
  
  <p>This one gets just an integer(ID of a Chief):</p>
  
  <pre><code>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
  </code></pre>