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)

enter image description here

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

score 13 · Accepted Answer · edited May 23 '17 at 12:09

13

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.

edited May 23 '17 at 12:09

Community

1
1

answered Nov 18 '12 at 08:57

Mahmoud Gamal

78,257
17
139
164

Does SQL server really allow a column alias to be specified with single quotes which denote character literals, not identifiers in SQL? – Nov 18 '12 at 09:21
@a_horse_with_no_name - As far as I know, Yes it allows a column alias to be specified with single quotes. But I wrote it this way to be formatted as a character literal for the output. But what is your recommendations in this? – Mahmoud Gamal Nov 18 '12 at 09:29
The SQL standard (and all other DBMS if I'm not mistaken) requires identifiers (that contain special characters like a space in this case) to be enclosed with double quotes. So a valid column alias should be written like this: `chief.Name as "Chief Name"`. As SQL Server also understands that syntax I prefer to stick with the standard. – Nov 18 '12 at 09:33

score 1 · Answer 2 · edited Nov 02 '14 at 23:01

1

This should work:

SELECT DEPARTMENT.Name, TEACHER.Name, TEACHER.IDCode
    , POST.Name, TEACHER.Tel, TEACHER.Salary, TEACHER.Rise, TEACHER.HireDate, c.Name as ChiefName
    FROM TEACHER
    join DEPARTMENT on TEACHER.ID_Department = DEPARTMENT.ID
    Join POST on TEACHER.ID_Post = POST.ID
    Left Join TEACHER c on c.ID=TEACHER.Chief

edited Nov 02 '14 at 23:01

Luis Gouveia

8,334
9
46
68

answered Nov 18 '12 at 08:59

bummi

27,123
14
62
101

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

2 Answers2