sql query to find employe without the mentioned roles

Josh 46 Reputation points
2022-04-25T12:26:16.84+00:00

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

196139-image.png

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,599 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Yitzhak Khabinsky 25,731 Reputation points
    2022-04-25T13:18:08.407+00:00

    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        |  
    +---------------+  
    
    0 comments No comments

  2. Naomi 7,366 Reputation points
    2022-04-25T14:32:11.683+00:00

    Try:

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

  3. Bert Zhou-msft 3,421 Reputation points
    2022-04-26T01:58:05.92+00:00

    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:
    196318-image.png
    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.

    0 comments No comments