Thursday, September 4, 2008

Query to show not match records

Query to show not match records

Two Table :-
Transaction Table 1(trans1):-

TDate1 trans_no
7/21/2008 451
7/21/2008 452
7/21/2008 453
7/21/2008 454
7/21/2008 455
7/21/2008 456
7/21/2008 457

Transaction Table 2(trans2):-

TDate2 trans_no
7/21/2008 451
7/21/2008 456
7/21/2008 561
7/21/2008 871
7/21/2008 981
7/21/2008 453

Query:-

Show Record Where trans1 trans_no is not match with trans2 trans_no but date are same

SELECT t1.TDate1,
t1.trans_no
from trans1 as t1
left join trans2 as t2
on t1.trans_no = t2.trans_no
where t2.trans_no is null
and t1.TDate1 between ‘2008-07-21′
and ‘2008-07-21′

No comments: