CODEWITHSUNDEEP
sql
user2984448
user2984448

Reputation: 25

Difference between 2 SQL queries from same table

Table SECURITYGROUPSID:

GROUPNAME    |    SIDNAME    
--------------------------
Group 1           Apple
Group 1           Apples
Group 1           Applesauce
Group 1           Applesauces
Group 1           Appleton
Group 2           Apple
Group 2           Applesauce
Group 2           Appleton

I need the difference in SIDNAME values between Groups 1 and 2 for values like 'Apple%'. For example, if I perform the following 2 queries, I need the result query at the bottom.

SELECT SIDNAME FROM SECURITYGROUPSID WHERE GROUPNAME = 'Group 1' AND SIDNAME LIKE 'Apple%';
SELECT SIDNAME FROM SECURITYGROUPSID WHERE GROUPNAME = 'Group 2' AND SIDNAME LIKE 'Apple%';

The result query should be: Apples Applesauces

Upvotes: 1

Views: 329

Answers (2)

Magnus
Magnus

Reputation: 46919

Just add an except (SqlServer) between your queries. (Or MINUS for Oracle)

SELECT SIDNAME FROM SECURITYGROUPSID WHERE GROUPNAME = 'Group 1' AND SIDNAME LIKE 'Apple%';
EXCEPT
SELECT SIDNAME FROM SECURITYGROUPSID WHERE GROUPNAME = 'Group 2' AND SIDNAME LIKE 'Apple%';

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

using not exists()

select o.sidname 
from securitygroupsid o
where o.groupname = 'Group 1' 
  and o.sidname like 'Apple%'
  and not exists (
    select 1
    from securitygroupsid i
    where i.groupname = 'Group 2'
      and i.sidname = o.sidname
      )

or using not in()

select o.sidname 
from securitygroupsid o
where o.groupname = 'Group 1' 
  and o.sidname like 'Apple%'
  and o.sidname not in (
    select i.sidname
    from securitygroupsid i
    where i.groupname = 'Group 2'
      and i.sidname like 'Apple%'
      )

or left join

select o.sidname 
from securitygroupsid o
  left join securitygroupsid i
    on o.sidname = i.sidname
   and o.groupname = 'Group 1'
   and i.groupname = 'Group 2'
where o.sidname like 'Apple%'
  and i.sidname is null

Upvotes: 2

Related Questions