Matching multiple columns from two tables

Hi all..
I have two tables such as cisco and ciscocom. and i wan to compare each
row of ciscocom with cisco having same column values. i wan to get the
count of matching columns for each row in cisco...
eg:
Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco has
columns:fw,ports,sec,des,tput etc. i wan the number of matching colum
for each row in ciscocom. please provide me with the procedure...
Waiting for your response...On 1 Mar 2006 03:01:21 -0800, Kuttan wrote:

>Hi all..
>I have two tables such as cisco and ciscocom. and i wan to compare each
>row of ciscocom with cisco having same column values. i wan to get the
>count of matching columns for each row in cisco...
>eg:
>Ciscocom has columns: Products,fw,ports,sec,des,tput etc and cisco has
>columns:fw,ports,sec,des,tput etc. i wan the number of matching colum
>for each row in ciscocom. please provide me with the procedure...
>Waiting for your response...
Hi Kuttan,
Not sure if I fully understand your requirements. If the answer below is
not what you're looking for, then please check www.aspfaq.com/5006 to
find out how to post CREATE TABLE statements, INSERT statements and
required results in order to get better help.
SELECT a.KeyColumn,
CASE WHEN a.DataColumn1 = b.DataColumn1 THEN 1 ELSE 0 END
+ CASE WHEN a.DataColumn2 = b.DataColumn2 THEN 1 ELSE 0 END
.....
+ CASE WHEN a.DataColumnN = b.DataColumnN THEN 1 ELSE 0 END
AS MatchCount
FROM Table1 AS a
INNER JOIN Table2 AS b
ON a.KeyColumn = b.KeyColumn
Hugo Kornelis, SQL Server MVP