Three-way FULL OUTER JOIN / table coalesce

Question

i want to perform a FULL OUTER JOIN, merging common rows, on three tables.

SELECT * FROM Users

id  Username  Fullname
==  ========  =====================
 7  iboyd     Ian Boyd
 8  nicholle  Michelle Karnac
10  jamie     Jimmy Chew

3 row(s) affected


SELECT * FROM GrobUsers

id  Username  Fullname
==  ========  =====================
 7  iboyd     Ian Steven Boyd 
 8  nicholle  Michelle Baker
 9  chris     Kris Kallme

3 row(s) affected


SELECT * FROM FrobUsers

id  Username  Fullname
==  ========  =====================
 7  ian       Ian
 9  chris     Kris K.
10  jamie     Jimmy Chew

3 row(s) affected

i want to merge the tables based on the id column.

This brings up the issue of how do i want conflicts resolved when the other column values differ. The algorithm that may be applied to resolve conflicts between Usernames and FullName is:

 if (id's are equal) then 
    pick one; i don't care

i've tried something along the lines of:

SELECT
   COALESCE(Users.id, GrobUsers.id, FrobUsers.id) AS id,
   COALESCE(Users.Username, GrobUsers.Username, FrobUsers.Username) AS Username,
   COALESCE(Users.FullName, GrobUsers.FullName, FrobUsers.FullName) AS Fullname
FROM Users
   FULL OUTER JOIN GrobUsers ON GrobUsers.id = Users.id

   FULL OUTER JOIN FrobUsers ON FrobUsers.id = .....something......

Answer 1

A typical trick -- use an aggregation function that doesn't make sense.

select id, min(username), min(fullname) from (
  SELECT * FROM Users 
  union
  SELECT * FROM FrobUsers
  union
  SELECT * FROM GrobUsers  
) as foo
group by foo.id

Hmmm... but then it may select user name from one table and full name from another table. If you still don't care, use that, otherwise... maybe

select id, username, fullname from (
  select id, username, fullname, takeme = row_number() over (partition by id)
  from (
    SELECT * FROM Users 
    union
    SELECT * FROM FrobUsers
    union
    SELECT * FROM GrobUsers  
  ) as foo
) as bar
where bar.takeme = 1

Answer 2

In the example you are giving you do not need joins at all. I hope, that this is a real example and not a contrived one. What you want to do is very simple here, you don't need joins at all and you don't need row_number. You can do it like this:

select id,Username,Fullname from Users
UNION ALL
select id,Username,Fullname from GrobUsers 
where id not in (select id from Users)
UNION ALL
select id,Username,Fullname from FrobUsers  
where id not in (select id from Users) and id not in (select id from GrobUsers)

And it will give you this:

id          Username   Fullname        
----------- ---------- -----------------
7           iboyd      Ian Boyd         
8           mkarnac    Michelle Karnac
9           kris       Kris Kallme 
10          jimmy      Jimmy Chew 

(4 row(s) affected)

Here is the test case I used:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Users]') AND type in (N'U'))
DROP TABLE [dbo].[Users]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GrobUsers]') AND type in (N'U'))
DROP TABLE [dbo].[GrobUsers]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FrobUsers]') AND type in (N'U'))
DROP TABLE [dbo].[FrobUsers]
GO

CREATE TABLE [dbo].[Users](
    [Id] [int] NOT NULL,
    [Username] [nchar](50) NULL,
    [Fullname] [nchar](50) NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[GrobUsers](
    [Id] [int] NOT NULL,
    [Username] [nchar](50) NULL,
    [Fullname] [nchar](50) NULL,
 CONSTRAINT [PK_GrobUsers] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[FrobUsers](
    [Id] [int] NOT NULL,
    [Username] [nchar](50) NULL,
    [Fullname] [nchar](50) NULL,
 CONSTRAINT [PK_FrobUsers] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO Users Values (7,'iboyd','Ian Boyd' )
INSERT INTO Users Values (8,'michelle','Michelle Karnac' )
INSERT INTO Users Values (10,'jimmy','Jimmy Chew' )


INSERT INTO [GrobUsers] Values (7,'iboyd','Ian Steven Boyd ' )
INSERT INTO [GrobUsers] Values (8,'michelle','Michelle Bachand' )
INSERT INTO [GrobUsers] Values (9,'kris','Kris Kallme' )

INSERT INTO [FrobUsers] Values (7,'iboyd','Ian' )
INSERT INTO [FrobUsers] Values (9,'michelle','Kris K.' )
INSERT INTO [FrobUsers] Values (10,'jimmy','Jimmy Chew' )
GO