'How to show notifications of submitted Qualifications to admin users only in sql?

I have a qualifications table and employeelogin table which has a role column that determines the users authority level, I'd like to show all the submitted qualifications to the admin users only so that they can verify or approve, so below is what I have done in my view, so the stored procedure needs to call this view to show notifications for admin users only:

SELECT  COUNT(EmployeeId) AS TotalTasks,'Approve ' AS TaskName FROM ( 

SELECT 
      EmployeeId
       FROM Qualification
WHERE   StatusId IN (1,4) --submitted status

 UNION ALL
SELECT 
      EmployeeId,RoleId
     FROM  EmployeeLogin  
    WHERE RoleId IN(1,8,5, 2) ---admin roles

)  as subquery
GROUP BY EmployeeId
sqlsql-serverstored-proceduressql-view


Solution 1:[1]

If I understand correctly, you can try to JOIN to filter admin user instead of UNION ALL

SELECT 
    COUNT(EmployeeId) TotalTasks,
    'Approve ' AS TaskName
FROM  EmployeeLogin  e
INNER JOIN Qualification q
ON e.EmployeeId = q.EmployeeId
WHERE e.RoleId IN(1,8,5, 2) AND q.StatusId IN (1,4)  ---admin roles

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 D-Shih

Related Questions