SQL Union but filter out Partial Duplicates

Question

This is a very simplified version of what I have.

I have 2 database tables People and Contacts. People is the original table but contacts is a new table that is used to override the data in the People table (these are different databases in the real scenario)

On initial Load all the people records are copied to the contacts table but after the initial Load new people can be added to the people or contact table as well as contacts set as deleted.

I want all the people returned but I want the contacts table to be the overriding one for example

example.

People Table

     PersonGuid | PersonId | FirstName   | LastName
     -----------|----------|-------------|-----
1      xxxx1    | 1        | John        | Smith
2      xxxx2    | 2        | Jane        | Doe
3      xxxx3    | 3        | John        | Doe
4      xxxx4    | 4        | Alice       | James
5      xxxx5    | 5        | Claire      | Wheeler

Contacts table (Jane Doe is set as deleted and John Doe doesn't exist due as was added after the intial import and Emma Green has been added. Deleted is NULL for ones that haven't been touched)

     ID         | LinkedId | FirstName   | LastName |  Deleted
     -----------|----------|-------------|----------|----------
1      xxxx1    | 1        | John        | Smith    |  NULL
2      xxxx2    | 2        | Jane        | Doe      |  1
3      xxxx3    | NULL     | Emma        | Green    |  0
4      xxxx4    | 4        | Alice       | James    |  0
5      xxxx5    | 5        | Claire      | Wheeler  |  NULL

What I want to Return is: (All records but with Jane Doe Set as Deleted as she is in the contacts table)

     ID         | LinkedId | FirstName   | LastName |  Deleted
     -----------|----------|-------------|----------|----------
1      xxxx1    | 1        | John        | Smith    |  NULL
2      xxxx2    | 2        | Jane        | Doe      |  1
3      xxxx3    | 3        | John        | Doe      |  0
4      xxxx4    | NULL     | Emma        | Green    |  0
5      xxxx5    | 4        | Alice       | James    |  0
6      xxxx6    | 5        | Claire      | Wheeler  |  NULL

But what I am getting is: (Jane Doe Twice)

     ID         | LinkedId | FirstName   | LastName |  Deleted
     -----------|----------|-------------|----------|----------
1      xxxx1    | 1        | John        | Smith    |  NULL
2      xxxx2    | 2        | Jane        | Doe      |  1
3      xxxx2    | 2        | Jane        | Doe      |  0
4      xxxx3    | 3        | John        | Doe      |  0
5      xxxx4    | NULL     | Emma        | Green    |  0
6      xxxx5    | 4        | Alice       | James    |  0
7      xxxx6    | 5        | Claire      | Wheeler  |  NULL

I am trying to Union the two tables but I don't know how to filter it to make the contacts data override the people data but also get all data from both tables?

Select PersonGuid as Id,
Personid,
Firstname,
LastName,
cast(0 as bit) as deleted
From DB1.People

Union

Select
Id,
LinkedId as PersonId,
FirstName,
LastName,
Deleted 
from DB2.Contacts

I have tried searching but I'm not sure what to search by so my terms may be wrong.

EDIT - The real query is no where near as simple as my example and the from is a join of about 6 tables on each query. I've tried to make a simple example to make it clear, but not sure if this has made the solution different?

Answer 1

It looks like you don't want a Union - rather you want a join. The FULL OUTER JOIN returns all rows for both tables, and will allow you to identify rows for the same person by joining on your ID and LinkedID.

You can then use the ISNULL function to choose from Contacts if a row exists, or Person if it does not.

Select ISNULL(C.ID,P.PersonGuid) as Id,
       ISNULL(C.LinkedID,Personid) as PersonId,
       ISNULL(C.Firstname,P.FirstName) as FirstName,
       ISNULL(C.Lastname,P.Lastname) as LastName,
       ISNULL(C.Deleted,cast(0 as bit)) as deleted
From DB1.People P
FULL OUTER JOIN DB2.Contacts C on P.PersonID = C.LinkedId

Answer 2

try with the below query.

Select PersonGuid as Id,
Personid,
Firstname,
LastName,
cast(0 as bit) as deleted
From DB1.People
where not exists (select 1 from Contacts WHERE personID=LinkedId and deleted=1)

Union

Select
Id,
LinkedId as PersonId,
FirstName,
LastName,
Deleted 
from DB2.Contacts

Answer 3

I think this might do what you want:

select pc.*
from (select pc.*,
             row_number() over (partition by firstname, lastname order by priority) as seqnum
from ((select ID, LinkedId, FirstName, LastName, Deleted, 1 as priority
             from contacts
            ) union all
            (select PersonGuid, PersonId, FirstName, LastName, 0 as Deleted, 2
             from persons
            )
           ) pc
     ) pc
where seqnum = 1;

I'm not sure how you identify the same person across tables. This uses the names, because that is how you describe the problem in words.

The idea is the following:

• Combine the rows from all the tables using UNION ALL (not UNION).
• Add a priority column.
• Use ROW_NUMBER() to choose the first value based on priority (the PARTITION BY is what changes if you use, say, the id to match between the tables).
• Finally, choose the rows where the row number is 1.

Answer 4

Can you please try this :

SELECT p.PersonGuid, 
    p.PersonId, 
    p.FirstName, 
    p.LastName, 
    CASE WHEN c.LinkedId IS NULL THEN 0 ELSE c.DELETED END deleted 
FROM people p
LEFT JOIN contact c ON c.LinkedId = p.PersonId
UNION
SELECT * FROM #contact a WHERE a.LinkedId IS NULL

Answer 5

UNION will give you the records with no duplicates, so to override the people data with the contact one, change the order of the queries:

Select Id, LinkedId as PersonId, FirstName, LastName, Deleted 
from DB2.Contacts

UNION

Select PersonGuid as Id,Personid,Firstname,LastName,cast(0 as bit) as deleted 
From DB1.People

To get all the records use the UNION ALL instead of UNION. This will return also the duplicates.