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