Query using a column which contains the value of another column

I have the following table in SQL Server 2019

UserID(PK) - string - 30 characters
Useremail  - string - 100 characters
ManagerUserID - string 30 characters

Note: ManagerUserID is nothing but the userid

The data is

1, [e-mail], 2  ( 2 is the manager of user 1)
2, two@manager,com, 3 ( 3 is the manager of user 2)
3, [e-mail], null (no manager as he is the boss)

How can we get user details and their manager details (they are just another user) by using one query?

These are my scripts

CREATE TABLE [dbo].[sampleusertable]
(
    [UserID] [varchar](50) NOT NULL,
    [Useremail] [varchar](50) NULL,
    [ManagerUserID] [varchar](50) NULL,

    CONSTRAINT [PK_sampleusertable] 
        PRIMARY KEY CLUSTERED ([UserID] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO [dbo].[sampleusertable] ([UserID], [Useremail], [ManagerUserID]) VALUES 
('1', '[e-mail]', '2'),
('2', '[e-mail]', '3'),
('3', '[e-mail]', NULL);
GO

Answer

You can use a subquery or a self JOIN to achieve this:

SELECT 
  a.UserID, 
  a.Useremail, 
  a.ManagerUserID, 
  (SELECT Useremail FROM sampleusertable WHERE UserID = a.ManagerUserID) AS ManagerEmail
FROM sampleusertable a

SELECT a.UserID,
  a.Useremail,
  a.ManagerUserID,
  b.Useremail AS ManagerEmail
FROM sampleusertable a
LEFT JOIN sampleusertable b ON a.ManagerUserID = b.UserID

Result:

| UserID | Useremail       | ManagerUserID | ManagerEmail     |
|--------|-----------------|---------------|------------------|
| 1      | [e-mail]     | 2             | [e-mail]  |
| 2      | [e-mail] | 3             | [e-mail]   |
| 3      | [e-mail]  | null          | null             |

Optionally, you can add COALESCE to fill in NULL values with something else like this:

SELECT a.UserID,
  a.Useremail,
  COALESCE(a.ManagerUserID, a.UserID) AS ManagerUserID,
  COALESCE(b.Useremail, a.Useremail) AS ManagerEmail
FROM sampleusertable a
LEFT JOIN sampleusertable b ON a.ManagerUserID = b.UserID

Result:

| UserID | Useremail       | ManagerUserID | ManagerEmail     |
|--------|-----------------|---------------|------------------|
| 1      | [e-mail]     | 2             | [e-mail]  |
| 2      | [e-mail] | 3             | [e-mail]   |
| 3      | [e-mail]  | 3             | [e-mail]   |

Fiddle here.