SQL: EXCEPT Query

Question

Here is a basic example of what I am trying to achieve:

create table #testing (
     tab varchar(max), a int, b int, c int )

insert into #testing VALUES ('x',1, 2, 3)
insert into #testing VALUES ('y',1, 2, 3)  
insert into #testing VALUES ('x', 4, 5, 6)  

select * from #testing

Which will Produce the table:

 tab     a    b    c
-----------------------
  x      1    2    3
  y      1    2    3
  x      4    5    6

I then want to compare rows on 'tab' based on the values of a,b,c:

select a,b,c from #testing where tab = 'x'
except
select a,b,c from #testing where tab= 'y'

Which gives me the answer I was expecting:

a    b    c
------------
4    5    6

However I want to also include the Tab column in my resultset, so I want somthing like this:

 Select tab,a,b,c from #testing where ????
            (select a,b,c from #testing where tab = 'x'
             except
             select a,b,c from #testing where tab= 'y')

How would I achieve this?

gzaxx · Accepted Answer · 2013-11-21 11:20:59Z

1

Use not exists:

select a.*
from #testing a
where a.tab = 'x' 
      and not exists (
                       select * 
                       from #testing t 
                       where t.a = a.a and t.b = a.b and t.c = a.c and t.tab = 'y'
                     )

And here you get SQL Fiddle demo: DEMO

answered Nov 21, 2013 at 11:20

gzaxx

17.6k2 gold badges40 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

NickyvV · Accepted Answer · 2013-11-21 11:27:49Z

1

Although the answer from @gzaxx does produce a correct result for this test data, the more generalized version is below, where I left 'x' and 'y' out of the statements.

select a.*
from #testing a
where not exists (
                   select * 
                   from #testing t 
                   where t.a = a.a and t.b = a.b and t.c = a.c and t.tab <> a.tab
                 )

answered Nov 21, 2013 at 11:27

NickyvV

1,7522 gold badges16 silver badges18 bronze badges

Comments

code save · Accepted Answer · 2013-11-21 11:33:10Z

1

Please Try it

 with cte as 
 (
 select *,rn = ROW_NUMBER() over(PARTITION by tab order by tab)from #testing 
 )
 select tab,a,b,c from cte where rn>1

answered Nov 21, 2013 at 11:33

code save

1,1061 gold badge9 silver badges15 bronze badges

Collectives™ on Stack Overflow

SQL: EXCEPT Query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related