Comparing 2 tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Comparing 2 tables

Author

Topic

mike1973
Starting Member

46 Posts

Posted - 2010-12-30 : 03:27:49

Hello guys,

i have 2 tables T1, T2 with the same structure in columns but may differ in rows. The structure is the following:
ID, CompanyID, YearID, MonthID, EmpID, Total

My Question is how i can find the Difference between the 2 tables Where
T1.Company=T2.Company AND
T1.YeardID=T2.YearID AND
T1.MonthID=T2.Month AND
T1.EmpID=T2.EmpID

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-30 : 03:54:36

select *
from T1
join T2 On
T1.Company=T2.Company AND
T1.YeardID=T2.YearID AND
T1.MonthID=T2.Month AND
T1.EmpID=T2.EmpID
and T1.Total = T2.Total
where T1.EmpID <> T2.EmpID

select *
from T1
full outer join T2 On
T1.Company=T2.Company AND
T1.YeardID=T2.YearID AND
T1.MonthID=T2.Month AND
T1.EmpID=T2.EmpID
and T1.Total = T2.Total
where T1.EmpID is null or T2.EmpID is null

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-30 : 23:50:31

I didnot carry out testing but I feel that one of the two condition should not be there.

quote:
Originally posted by nigelrivett

select *
from T1
join T2 On
T1.Company=T2.Company AND
T1.YeardID=T2.YearID AND
T1.MonthID=T2.Month AND
T1.EmpID=T2.EmpID
and T1.Total = T2.Total
where T1.EmpID <> T2.EmpID

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

X002548
Not Just a Number

15586 Posts

Posted - 2010-12-31 : 00:01:57

.....bombay trading Co?

I'll have a case of quinine and Gin

You might wan t o post ddl, sample DML and expected results

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-31 : 01:00:05

quote:
Originally posted by pk_bohra

I didnot carry out testing but I feel that one of the two condition should not be there.

quote:
Originally posted by nigelrivett

select *
from T1
join T2 On
T1.Company=T2.Company AND
T1.YeardID=T2.YearID AND
T1.MonthID=T2.Month AND
T1.EmpID=T2.EmpID
and T1.Total = T2.Total
where T1.EmpID <> T2.EmpID

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Sorry - that one was meant to be

select *
from T1
join T2 On
T1.Company=T2.Company AND
T1.YeardID=T2.YearID AND
T1.MonthID=T2.Month AND
T1.EmpID=T2.EmpID
where T1.Total <> T2.Total

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources