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

ahmed salah

2y842SQL

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

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)
GO

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ashutosh Singh2y ago
```
BEGIN TRY
    SELECT RequestNo,
           CASE
               WHEN REAN82 IS NOT NULL AND REAN83 IS NULL AND REAN84 IS NULL THEN 'REQ'
               WHEN REAN82 IS NULL AND REAN83 IS NOT NULL AND REAN84 IS NULL THEN 'LM'
               WHEN REAN82 IS NULL AND REAN83 IS NULL AND REAN84 IS NOT NULL THEN 'DM'
               WHEN REAN82 IS NULL AND REAN83 IS NOT NULL AND REAN84 IS NOT NULL THEN 'LDM'
               ELSE NULL 
           END AS Role
    FROM dbo.F6000059;
END TRY
BEGIN CATCH
    -- Handle the error here (e.g., logging or reporting the error)
    PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH;
```
Hi @ahmed-salah25,

This query will select the RequestNo and use the CASE statement to determine the 'Role' based on the conditions you specified, with 'REQ' for the first condition, 'LM' for the second condition, 'DM' for the third condition, and 'LDM' for the fourth condition. Other cases that don't match any of these conditions will have a 'NULL' value in the 'Role' column.

Suppose your dbo.F6000059 table has the following sample data:

| RequestNo | REAN82 | REAN83 | REAN84 |
|-----------|--------|--------|--------|
| 1 | value1 | NULL | NULL |
| 2 | NULL | value2 | NULL |
| 3 | NULL | NULL | value3 |
| 4 | NULL | value4 | value4 |
| 5 | value5 | value5 | NULL |
| 6 | NULL | NULL | NULL |

Then the output of the SQL query will be:

| RequestNo | Role |
|-----------|------|
| 1 | REQ |
| 2 | LM |
| 3 | DM |
| 4 | LDM |
| 5 | NULL |
| 6 | NULL |

Tahir Ansari2y ago

SELECT RequestNo,
       CASE
           WHEN REAN82 IS NOT NULL AND REAN83 IS NULL AND REAN84 IS NULL THEN 'REQ'
           WHEN REAN82 IS NULL AND REAN83 IS NOT NULL AND REAN84 IS NULL THEN 'LM'
           WHEN REAN82 IS NULL AND REAN83 IS NULL AND REAN84 IS NOT NULL THEN 'DM'
           WHEN REAN82 IS NULL AND REAN83 IS NOT NULL AND REAN84 IS NOT NULL THEN 'LDM'
           ELSE NULL -- Handle other cases (optional)
       END AS Role
FROM dbo.F6000059