locked
Select uncommon rows based on single column in MSSQL or LINQ RRS feed

  • Question

  • I want to select a API_PATH which is not common between ADMIN and SUPER ADMIN role.

    ╔════════╦═════════════════════╦═════════════╗
     API_ID       API_PATH         ROLE_NAME  
    ╠════════╬═════════════════════╬═════════════╣
          1  /objects/Types       Super Admin 
          2  /objects/Types       Admin       
          3  /organization/list   Super Admin 
          4  /objects/Types/6498  Super Admin 
          5  /objects/Types/6498  Admin       
    ╚════════╩═════════════════════╩═════════════╝

    The desired result should be:

    ╔════════╦════════════════════╦═════════════╗
     API_ID       API_PATH        ROLE_NAME  
    ╠════════╬════════════════════╬═════════════╣
          3  /organization/list  Super Admin 
    ╚════════╩════════════════════╩═════════════╝

    In the above /organization/list is only API_PATH which is not common in both roles. How can i do it using MSSQL or LINQ?

    Monday, May 11, 2020 4:27 PM

All replies

  • Please try this 

    Declare @tab table(API_ID int,API_PATH varchar(100),ROLE_NANE varchar(100))

    insert into @tab select 1 ,'/objects/Types','Super Admin'
    insert into @tab select 2 ,'/objects/Types','Admin'
    insert into @tab select 3 ,'/organization/list' ,'Super Admin'
    insert into @tab select 4 ,'/objects/Types/6498','Super Admin'
    insert into @tab select 5 ,'/objects/Types/6498','Admin'

    select a.API_PATH from 

    (

    (select * from @tab  WHERE ROLE_NANE = 'Super Admin') a
    Left join 
    (select * from @tab  WHERE ROLE_NANE = 'Admin') b
    on a.API_PATH = b.API_PATH

    where  
     
    b.API_PATH IS NULL


    http://uk.linkedin.com/in/ramjaddu

    Monday, May 11, 2020 4:44 PM