Query using a column which contains the value of another column-CodePudding

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, [email protected], 2  ( 2 is the manager of user 1)
2, two@manager,com, 3 ( 3 is the manager of user 2)
3, [email protected], 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', '[email protected]', '2'),
('2', '[email protected]', '3'),
('3', '[email protected]', NULL);
GO

CodePudding user response：

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      | [email protected]     | 2             | [email protected]  |
| 2      | [email protected] | 3             | [email protected]   |
| 3      | [email protected]  | 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      | [email protected]     | 2             | [email protected]  |
| 2      | [email protected] | 3             | [email protected]   |
| 3      | [email protected]  | 3             | [email protected]   |

Fiddle here.