CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Shamrockonov
Shamrockonov

Reputation: 489

MS SQL retrieve related data from same table in one query

I have a table called mc.staff which in its simplest form is

id | name | supervisorId

I need a query which will pull out the id and name of a person, but also the id and name of their supervisor in one row. I have been told using some sort of self join to the table would work but I can't seem to figure out the logic behind it. Any pointers in the right direction would be appreciated.

Upvotes: 0

Views: 1243

Answers (1)

Chun Lin
Chun Lin

Reputation: 544

You can choose to use INNER JOIN here. INNER JOIN is used to combine rows from two or more tables, based on a common field between them, as long as there is a match in BOTH tables. The similarity that we will be using here is the supervisorId of mc.staff and the id of mc.staff.

Thus, the following query will return the results.

SELECT A.id, A.name, B.id, B.name
FROM mc.staff A
INNER JOIN mc.staff B ON A.supervisorId = B.id

Please correct me if I am wrong.

Upvotes: 1

Related Questions