sql query to find employe without the mentioned roles

Question

i have view which has both employee and roles information. i'm tryting to find employe name who are does not have IT, finance roles

Answer 1

Hi @Josh ,

The problem definition is called a Relational Division.
Check it out here: divided-we-stand-the-sql-of-relational-division

SQL

USE tempdb;  
GO  
  
-- DDL and sample data population, start  
DROP TABLE IF EXISTS dbo.EmployeeRole;  
DROP TABLE IF EXISTS dbo.Roles;  
  
CREATE TABLE dbo.EmployeeRole  
(  
 employee_name CHAR(15) NOT NULL,  
 role CHAR(15) NOT NULL,  
 PRIMARY KEY (employee_name, [role])  
);  
INSERT INTO dbo.EmployeeRole (employee_name, role) VALUES  
('Higgins', 'IT'),  
('Higgins', 'Finance'),  
('Jones'  , 'IT'),  
('Jones'  , 'Finance'),  
('Jones'  , 'Science'),  
('Smith'  , 'IT'),  
('Wilson' , 'Science'),  
('Wilson' , 'Finance'),  
('Helen' , 'Science');  
  
CREATE TABLE dbo.Roles  
(  
 role CHAR(15) NOT NULL PRIMARY KEY  
);  
INSERT INTO dbo.Roles (role) VALUES  
('IT'),  
('Finance');  
-- DDL and sample data population, end  
  
-- Relational Division with a Remainder  
SELECT DISTINCT employee_name  
  FROM dbo.EmployeeRole AS PS1   
  WHERE EXISTS  
       (SELECT *  
          FROM dbo.Roles  
         WHERE NOT EXISTS  
               (SELECT *  
                  FROM dbo.EmployeeRole AS PS2  
                 WHERE (PS1.employee_name = PS2.employee_name)  
                   AND (PS2.role = Roles.role)));  

``Output

+---------------+  
| employee_name |  
+---------------+  
| Helen         |  
| Smith         |  
| Wilson        |  
+---------------+  

Answer 2

Try:

SELECT Employee_name FROM #EmployeeRole
 EXCEPT
 SELECT Employee_name FROM #EmployeeRole
 WHERE [Role] IN ('IT', 'Finance')

Answer 3

Hi，@Josh

Welcome to Microsoft T-SQL Q&A Forum!

There are many ways to implement filter values . Using Except and exists , the idea of these methods is the same : find matching fields .
Please check this:
DDL：

create table EmployeeRole  
(  employee nvarchar(10) ,  
   role nvarchar(10)  
)  
 INSERT INTO EmployeeRole VALUES  
 ('aaaa', 'IT'),  
 ('aaaa', 'Finance'),  
 ('bbb'  , 'IT'),  
 ('bbb'  , 'Finance'),  
 ('bbb'  , 'Science'),  
 ('ccc'  , 'IT'),  
 ('ddd' , 'Science'),  
 ('ddd' , 'Finance'),  
 ('eee' , 'Science')  

Query：

alter view EmployeeRole_view   
 as  
 SELECT *  
FROM EmployeeRole  
WHERE employee NOT IN (SELECT employee FROM EmployeeRole WHERE role IN('IT','Finance'))  
   
 select * from EmployeeRole_view  

OUTPUT：

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.