sql-serversql-server-2000full-outer-join

Three-way FULL OUTER JOIN / table coalesce


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......

Solution

  • 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