MSSQL - Join the same table multiple times in one simple query

Watch this example on YouTube



1. Table

USE [Company]
GO
/****** Object:  Table [dbo].[TestJoin]    Script Date: 2019-06-06 7:02:25 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestJoin](
    [Name] [varchar](50) NULL,
    [Manager] [varchar](50) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[TestJoin] ([Name], [Manager]) VALUES (N'Bob', NULL)
GO
INSERT [dbo].[TestJoin] ([Name], [Manager]) VALUES (N'Frank', N'Bob')
GO
INSERT [dbo].[TestJoin] ([Name], [Manager]) VALUES (N'Ann', N'Bob')
GO
INSERT [dbo].[TestJoin] ([Name], [Manager]) VALUES (N'Rob', N'Frank')
GO
INSERT [dbo].[TestJoin] ([Name], [Manager]) VALUES (N'Eddy', N'Frank')
GO
INSERT [dbo].[TestJoin] ([Name], [Manager]) VALUES (N'John', N'Rob')
GO

2. Query

Select TestJoin.Name As Boss ,
TestJoin2.Name As Director,
TestJoin3.Name As Manager,
TestJoin4.Name As Nobody


From TestJoin
Left Outer Join TestJoin As TestJoin2 On TestJoin.Name = TestJoin2.Manager
Left Outer Join TestJoin As TestJoin3 On TestJoin2.Name = TestJoin3.Manager
Left Outer Join TestJoin As TestJoin4 On TestJoin3.Name = TestJoin4.Manager

Where TestJoin.Name = 'Bob'