Multiple ID check

Question

i have Multiple Role ID For Single Emplyee with different rows. I want to check each roleid exist or not in another table with different rows

Brahma Prakash Shukla · Answer

Assuming you have the following two tables: Table EmployeeRoles : EmployeeID RoleID 1 101 1 102 1 103 2 101 2 104 3 102 Table AvailableRoles : RoleID 101 102 103 You want to check if each RoleID associated with an EmployeeID in the EmployeeRoles table exists in the AvailableRoles table. Here's a SQL query that can help you achieve this: SELECT ER.EmployeeID, ER.RoleID, CASE WHEN AR.RoleID IS NOT NULL THEN 'Exists' ELSE 'Not Exists' END AS RoleStatus FROM EmployeeRoles ER LEFT JOIN AvailableRoles AR ON ER.RoleID = AR.RoleID; This query uses a LEFT JOIN between the EmployeeRoles and AvailableRoles tables on the RoleID column. It selects the EmployeeID and RoleID columns from the EmployeeRoles table and checks whether a match is found in the AvailableRoles table. If a match is found (RoleID exists), the RoleStatus column will show "Exists"; otherwise, it will show "Not Exists". Keep in mind that the above query assumes that you want to check all role IDs for each employee. If you want to check only specific role IDs or employees, you can modify the query accordingly using WHERE clauses. Please replace table and column names with your actual table and column names if they are different in your database.

Tasadduq Burney · Answer

SELECT DISTINCT ER.EmployeeID, ER.RoleID, CASE WHEN RR.RoleID IS NOT NULL THEN 'Exists' ELSE 'Does Not Exist' END AS Status FROM EmployeeRoles ER LEFT JOIN RoleReference RR ON ER.RoleID = RR.RoleID;

Abhishek Yadav · Answer

SELECT EmpID, RoleID, EXISTS ( SELECT 1 FROM [AnotherTable] WHERE RoleID = [ThisTable].RoleID ) AS RoleIDExists FROM [ThisTable]

Amit Mohanty · Answer

SELECT ER.EmployeeID, ER.RoleID, CASE WHEN R.RoleID IS NOT NULL THEN 'Exists' ELSE 'Does Not Exist' END AS RoleStatus FROM EmployeeRoles ER LEFT JOIN Roles R ON ER.RoleID = R.RoleID WHERE ER.EmployeeID = ;

Cr Bhargavi · Answer

SELECT DISTINCT EmployeeID FROM EmployeeRolesTable e WHERE EXISTS ( SELECT 1 FROM OtherTable o WHERE o.RoleID = e.RoleID )

Multiple ID check

Answers (5)