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...Kuttan
SELECT cisco .*, ciscocom.*
FROM cisco
FULL OUTER JOIN
ciscocom
ON cisco .c1 = ciscocom.c1
AND cisco .c2 = ciscocom.c2
..
AND cisco .cn = ciscocom.cn
WHERE cisco .key IS NULL OR ciscocom.key IS NULL
"Kuttan" <vvyshak@.gmail.com> wrote in message
news:1141210889.041213.70320@.v46g2000cwv.googlegroups.com...
> 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...
>|||thanks a lot..
But it doesnt worked.
Actually i wan the columns that matches with each rows of the cisco
from ciscocom...
Anyway thanx a lot for your response...
thank u so much..|||Hope this is what you're looking for
SELECT COUNT (cisco.Products)
FROM cisco INNER JOIN ciscocom
ON
cisco.Products=cisco.Products AND
cisco.fw=cisco.fw AND
cisco.ports=cisco.ports
/*
* If you want to specify a value for a column
*
*/
WHERE cisco.Products='your_value'

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