MittyKom
3/8/2007 7:46:00 PM
Thank you so much.
"Alejandro Mesa" wrote:
> MittyKom,
>
> Try using NOT EXISTS.
>
> select *
> from Tab2 as t2
> where not exists (
> select *
> from Tab1 as t1
> where t1.id1 = t2.id1 and t1.id2 = t2.id2
> )
>
> In version 2005 you can use also EXCEPT operator.
>
> select id1, id2 from Tab2
> EXCEPT
> select id1, id2 from Tab1;
>
>
> AMB
>
>
> "MittyKom" wrote:
>
> > Hi All
> >
> >
> > I have two tables that are supposed to have the same records and i would
> > like to get those records in Tab2 that are not in Tab1. The records are
> > uniquely identified by a combination of column Id1 and Id2 in both the
> > tables. Below is what i have tried and i am not getting the records:
> >
> > select tab2.id1 ,tab2.id2 from tab2 ,(select tab2.id1 ,tab2.id2 from tab1
> > inner join tab2 on tab1.id1 = tab2.id1 and tab1.id2 = tab2.id2)P
> > where P.id1 <> tab2.id1 and P.id2 <> tab2.id2
> >
> > Resultset is supposed to have:
> > 'd','1','a1'
> >
> > --Table1
> > create table Tab1 (id1 varchar(5) NOT NULL, id2 varchar(5) NOT NULL, Dt
> > varchar(20))
> > --
> > insert into Tab1 values ('a','1','a1')
> > insert into Tab1 values ('a','2','b2')
> > insert into Tab1 values ('c','3','c3')
> >
> > --Table2
> > create table Tab2 (id1 varchar(5) NOT NULL, id2 varchar(5) NOT NULL, Dt
> > varchar(20))
> > --
> > insert into Tab2 values ('a','1','a1')
> > insert into Tab2 values ('a','2','b2')
> > insert into Tab2 values ('c','3','c3')
> > insert into Tab2 values ('d','1','a1')
> >
> > select * from tab1
> > select * from tab2
> >
> > What would be the best way to achieve this? Thank you in advance.