CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2014
Daniel_000000
Daniel_000000

Reputation: 21

Count coincidences between rows in two tables

I am trying to write a query in SQL Server 2014 that counts the number of coincidences (but just when those coincidences are more than 1) between the rows in two tables, into a new column. The structure is like this:

table_a:
================
c1 | c2 | c3 | c4

11 | 30 | 70 | 90
20 | 80 | 95 | 99
15 | 39 | 40 | 41
...

table_b:
================
n1 | n2 | n3 | n4

30 | 65 | 90 | 95
20 | 80 | 90 | 99
15 | 80 | 95 | 99
...

And I want to add a column in a query after the columns in table_a like this:

query:
================
c1 | c2 | c3 | c4 | c5

11 | 30 | 70 | 90 | 2
20 | 80 | 95 | 99 | 6
15 | 39 | 40 | 41 | 0
...

In other words, for each row in table_a check how many numbers match each row in table_b , afterwards sum all results greater than 1 and paste next to the row in table_a.

I have the following code but I get wrong output:

SELECT TOP (100) PERCENT dbo.table_a.c1, dbo.table_a.c2,  
    dbo.table_a.c3, dbo.table_a.c4, dbo.table_a.c5, dbo.table_a.c6,  
    COUNT('c1 = n1 or n2 or n3 or  n4 or  n5 or  n6')  
    + COUNT('c2 = n1 or n2 or n3 or n4 or  n5 or  n6')  
    + COUNT('c3 = n1 or n2 or n3 or  n4 or  n5 or  n6')  
    + COUNT('c4 = n1 or n2 or n3 or  n4 or  n5 or  n6')  
    + COUNT('c5 = n1 or n2 or n3 or  n4 or  n5 or  n6')  
    + COUNT('c6 = n1 or n2 or n3 or  n4 or  n5 or  n6') AS c5  
FROM  dbo.table_a CROSS JOIN dbo.[table_b]  
GROUP BY dbo.table_a.c1, dbo.table_a.c2, dbo.table_a.c3, dbo.table_a.c4,  
  dbo.table_a.c5, dbo.table_a.c6  
HAVING   (COUNT('c1 = n1 or n2 or n3 or  n4 or  n5 or  n6')  
    + COUNT('c2 = n1 or n2 or n3 or  n4 or  n5 or  n6')  
    + COUNT('c3 = n1 or n2 or n3 or  n4 or  n5 or  n6')  
    + COUNT('c4 = n1 or n2 or n3 or  n4 or  n5 or  n6')  
    + COUNT('c5 = n1 or n2 or n3 or  n4 or  n5 or  n6')   
    + COUNT('c6 = n1 or n2 or n3 or  n4 or  n5 or  n6') > 1)
ORDER BY c5 DESC

Any help would be appreciated, thx!

Upvotes: 2

Views: 643

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

;with cte(n) as(
    select n1 from table_b union all
    select n2 from table_b union all
    select n3 from table_b union all
    select n4 from table_b
)
,counted(n, c) as(
    select n, c = count(n) from cte group by n
)
select
    a.*, b.cc
from table_a a
cross apply(
    select cc = sum(case when c > 1 then c else 0 end)
    from counted
    where
        n = a.c1
        or n = a.c2
        or n = a.c3
        or n = a.c4
)b

Upvotes: 2

Related Questions