scrayon scrayon - 3 months ago 7
SQL Question

Split or identify records based off number of join/where conditions satisfied

Say I have 2 tables that are joined on 5 fields that exist in both tables:

firstname
lastname
address
city
country


What is the most efficient way in determining records where all match, any 4 match, any 3 match, any 2 match, 1 match, no match.

Originally, I was thinking of just puting all 5 in the join condition with AND so I would get the all 5 match records and 0 match records (I used left join and would check for null on one of the fields in the right table). From the list of Null's I could check for each condition using the same left join and union them all filtering the values with null's or the # of nulls to determine my matches.

I am sure there is a better way in doing this. Any suggestions?

Thanks,

Answer

Use a CROSS JOIN that creates a full cross-product between the two tables. Then add up the number of columns that match between each pair of records.

SELECT t1.*, t2.*, 
    t1.firstname = t2.firstname + t1.lastname = t2.lastname + t1.address = t2.address + t1.city = t2.city + c1.country = t2.country AS num_fields_matching
FROM Table1 AS t1
CROSS JOIN Table2 AS t2