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

Paintbox
Starting Member

1 Post

Posted - 2011-11-28 : 16:44:26

I have 2 tables each with the same 12 columns, named Pair01 to Pair012
I would like to compare the same named columns of each table and if the contents are the same I would like to allocate a score of 1, if they are unequal the score is 0. I would then like to produce a Total score which would be a sum of all the 1's with a possible high score of 12.

I have to program this in ASP connected to an Access data base, any help would be appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-29 : 03:13:20

quote:
Originally posted by Paintbox

I have 2 tables each with the same 12 columns, named Pair01 to Pair012
I would like to compare the same named columns of each table and if the contents are the same I would like to allocate a score of 1, if they are unequal the score is 0. I would then like to produce a Total score which would be a sum of all the 1's with a possible high score of 12.

I have to program this in ASP connected to an Access data base, any help would be appreciated

if its access query you're looking at please post in access forums.
B/w does your tables have any common column by which they can be related?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sql-programmers
Posting Yak Master

190 Posts

Posted - 2011-12-01 : 07:25:55

I have created sample table with two column, It may help solve your problem

create table t1 (c1 int, c2 int)
create table t2 (c1 int, c2 int)
insert into t1 Values(1, 1)
insert into t1 Values(2, 2)
insert into t1 Values(3, 3)
insert into t2 Values(1, 1)
insert into t2 Values(2, 1)

select * from t1
select * from t2

select t1.c1, t1.c2, t2.c1, t2.c2 from t1
inner join t2 on t1.c1 = t2.c1 and t1.c2 = t2.c2

To Get Matched column count
--------------------------------------------------------
select COUNT(*) from t1
inner join t2 on t1.c1 = t2.c1 and t1.c2 = t2.c2

To Get UnMatched column count
--------------------------------------------------------
select COUNT(*) from t1
left join t2 on t1.c1 = t2.c1 and t1.c2 = t2.c2
where t2.c1 is null and t2.c2 is null

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

sureshkk
Starting Member

21 Posts

Posted - 2011-12-01 : 08:08:49

--1) CREATE Temp Tables with actual table data by adding new identity column Sno

SELECT IDENTITY(INT,1,1) Sno,Pair01,Pair02,Pair03 INTO #tbl1 FROM Table1

SELECT IDENTITY(INT,1,1) Sno,Pair01,Pair02,Pair03 INTO #tbl2 FROM Table2

--2) Join the Temp tables using Sno Column and Compare the colum values
--if the values are matched assign 1 else assign 0 end sum up all the values.
SELECT CASE WHEN a.Pair01=b.Pair01 THEN 1 ELSE 0 END+
CASE WHEN a.Pair02=b.Pair02 THEN 1 ELSE 0 END+
CASE WHEN a.Pair03=b.Pair03 THEN 1 ELSE 0 END
FROM #tbl1 a
INNER JOIN #tbl2 b ON a.Sno=b.Sno

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources