I have the simple requirement, there are the 3 SQL Server tables like this:
Table 1; columns
xAID Name DOB Gender PostCode
Table 2; columns
BID Name DOB Gender PostCode
Table 3; columns
CID Name DOB Gender PostCode
I wish to join (inner joins) based on Name, DOB, Gender & PostCode across all the tables, if the record matches I would like to put the result in one table and the remaining records I wish to put in another table for further comparison/processing.
The first part is simple with this query:
SELECT AID, BID, CID, A.Name, A.DOB, A.Gender, A.PostCode
FROM TAB_A
JOIN TAB_B B ON A.Name = B.Name
AND A.DOB = B.DOB
AND A.Gender = B.Gender
AND A.PostCode = B.PostCode
JOIN TAB_C C ON A.Name = C.Name
AND A.DOB = C.DOB
AND A.Gender = C.Gender
AND A.PostCode = C.PostCode
But 2nd part on the remaining records which doesn’t match need to put them in a separate table.
For example if there are total 50 records in all the 3 tables (A = 20, B=20 & C=10) and the output where all the records are matching based on the above query are 5, I wish to store 95 records in the separate table.
Your help/answer will be appreciated.
Thanks
Advertisement
Answer
You can use union all
to combine the tables. Then use a window function to count the matches by the columns you care about.
Return the rows where the match is less than 3:
select id, Name, DOB, Gender, PostCode
from (select id, Name, DOB, Gender, PostCode,
count(*) over (partition by Name, DOB, Gender, PostCode) as cnt
from ((select AID as id, Name, DOB, Gender, PostCode
from a
) union all
(select BID, Name, DOB, Gender, PostCode
from b
) union all
(select CID, Name, DOB, Gender, PostCode
from c
)
) abc
) abc
where cnt < 3;