How to make query return only one role based on employee no from three column ?

Question

I need to make sql query return only one role from table dbo.F6000059 based on employee no but I face issue I don't know how to return correct role

I will return only one role from table dbo.F6000059 if employee no exist on columns REAN82 OR REAN83 OR REAN84 .

And role name will return will be only one roles from below :

REQ

LM

DM

LDM

condition I will write :

if employee no exist on column REAN82 only then role will be REQ .

if employee no exist on column REAN83 only then role will be LM .

if employee no exist on column REAN84 only then role will be DM.

if employee no exist on column REAN84 AND column REAN83 only then role will be LDM.

scripts for table as below :

USE [Test]
GO
/****** Object:  Table [dbo].[F6000059]    Script Date: 20/10/2023 9:43:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[F6000059](
	[RequestNo] [int] NULL,
	[REAN82] [int] NULL,
	[REAN83] [int] NULL,
	[REAN84] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[F6000059] ([RequestNo], [REAN82], [REAN83], [REAN84]) VALUES (12001, 134618, NULL, NULL)
INSERT [dbo].[F6000059] ([RequestNo], [REAN82], [REAN83], [REAN84]) VALUES (12002, 134618, 988144, 390144)
INSERT [dbo].[F6000059] ([RequestNo], [REAN82], [REAN83], [REAN84]) VALUES (12003, NULL, 977133, NULL)
INSERT [dbo].[F6000059] ([RequestNo], [REAN82], [REAN83], [REAN84]) VALUES (12004, 977133, 200312, 950188)
INSERT [dbo].[F6000059] ([RequestNo], [REAN82], [REAN83], [REAN84]) VALUES (12005, 881099, 977133, 504122)
INSERT [dbo].[F6000059] ([RequestNo], [REAN82], [REAN83], [REAN84]) VALUES (12006, 120442, NULL, 504122)
INSERT [dbo].[F6000059] ([RequestNo], [REAN82], [REAN83], [REAN84]) VALUES (12007, 504122, 977133, 394421)
INSERT [dbo].[F6000059] ([RequestNo], [REAN82], [REAN83], [REAN84]) VALUES (12008, 407133, NULL, 303144)
INSERT [dbo].[F6000059] ([RequestNo], [REAN82], [REAN83], [REAN84]) VALUES (12009, 503144, 407133, 407133)
INSERT [dbo].[F6000059] ([RequestNo], [REAN82], [REAN83], [REAN84]) VALUES (12010, 761300, 407133, 905154)
INSERT [dbo].[F6000059] ([RequestNo], [REAN82], [REAN83], [REAN84]) VALUES (12011, 407133, 390222, 395222)
GO

desired result is return only role REQ OR LM OR DM OR LDM based on employee no as image below :

what i try but get wrong result as below

correct must be LDM

BECAUSE employee no 407133 exist on two columns rean83 and rean84

SELECT top 1 Role = CASE
                  WHEN f.REAN82 IS NOT NULL THEN
                      'REQ'
                  WHEN f.REAN83 IS NOT NULL
                       AND f.REAN84 IS NOT NULL THEN
                      'LDM'
                  WHEN f.REAN83 IS NOT NULL THEN
                      'LM'
                  WHEN f.REAN83 IS NOT NULL THEN
                      'DM'
                  ELSE
                      'Not found'
              END
FROM dbo.F6000059 f
where REAN82=407133 or REAN83=407133 or REAN84=407133
ORDER BY f.RequestNo;

Answer 1

Here is a simple-minded query, where I have added the employee numbers to table variable to make things easier. I assume that in your real database, you have an Employees table in some shape of form.

DECLARE @Employees TABLE (EmployeeID int NOT NULL PRIMARY KEY)
INSERT @Employees(EmployeeID)
   VALUES(134618), (977133), (504122), (407133)

SELECT F.RequestNo, E.EmployeeID, 
       Role = CASE WHEN F.REAN82 = E.EmployeeID AND isnull(F.REAN83, -1) <> E.EmployeeID AND isnull(F.REAN84, -1) <> E.EmployeeID THEN 'REQ'
                   WHEN isnull(F.REAN82, -1) <> E.EmployeeID AND F.REAN83 =  E.EmployeeID AND isnull(F.REAN84, -1) <> E.EmployeeID THEN 'LM'
                   WHEN isnull(F.REAN82, -1) <> E.EmployeeID AND isnull(F.REAN83, -1) <> E.EmployeeID AND F.REAN84 = E.EmployeeID THEN 'DM'
                   WHEN isnull(F.REAN82,-1)  <> E.EmployeeID AND F.REAN83 = E.EmployeeID AND F.REAN84 = E.EmployeeID THEN 'LDM'
              END
FROM  @Employees E
CROSS APPLY (SELECT TOP 1 F.*
             FROM   F6000059 F
             WHERE  E.EmployeeID IN (F.REAN82, F.REAN83, F.REAN84)
             ORDER  BY F.RequestNo) AS F
ORDER BY F.RequestNo;

However, just like your query, it returns REQ for 407133 and LM. This is because on the first row that 407133 appears on is Request No 12008, and on this row 407133 is in column REAN82 and whence you get REQ. I can guess from your colouring that there may be additional business rules that rules out this row as a match for 407133, but since I don't know those business rules, I can't do better than this for the moment.