How do I get the Name of two different ID in 1 table-CodePudding

So I have a table with columns:

UID_person, UID_personHead, Firstname, LastName

The value of UID_personhead is equivalent to UID_person. UID_personHead is the UID_person of managers So I want to query it and return something like

UID_person	FirstName	LastName	Name of manager using the UID_personHead
1	Sarah	Smith	Jack
2	Jacob	Smith	David

I tried this query

SELECT UID_Person, FirstName, LastName
(SELECT FirstName
from person WHERE UID_person IN (SELECT UID_PersonHead From person)) AS Name of the manager

CodePudding user response：

Just self join the table back to itself on UID_PersonHead

SELECT p.UID_Person, p.FirstName, p.LastName, m.FirstName
FROM person p
LEFT JOIN person m ON m.id = p.UID_PersonHead;

CodePudding user response：

your data is like this

declare @a table
(UID_person int, UID_personHead int, Firstname varchar(30), LastName varchar(30))
insert into @a 
(UID_person,UID_personHead,Firstname,LastName) values
(1 ,3,'Sarah','Smith'),
(2 ,4,'Jacob','Smith'),
(3 ,3,'Jack','johnson'),
(4 ,4,'David','johnson');

your need to join your table with itself

select a0.UID_person, a0.Firstname, a0.LastName,a1.Firstname  
from @a a0
join @a a1
on a0.UID_personHead=a1.UID_person
where a1.UID_personHead<>a0.UID_person