SQL Server Full Join – How to Align Matches Between Two Tables

join;sql serversql-server-2008-r2t-sql

I am working with a system that has two databases. One for the ERP system and one for the website. Both DB's have tables for alternate codes. I am trying to write a query that will show me differences and matches between the two db's alternate code tables.

To setup my environment here is a small sample:

CREATE TABLE [dbo].[alt_code_ERP](
    [UID] [int] NOT NULL,
    [item_id] [nchar](10) NOT NULL,
    [alternate_code_ERP] [nvarchar](50) NOT NULL,
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[web_alt_code](
    [UID] [int] NOT NULL,
    [item_id] [nchar](10) NOT NULL,
    [web_alternate_code] [nchar](50) NOT NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[alt_code_ERP] ([UID],[item_id],[alternate_code_ERP]) VALUES (1, 'part1' , 'part1_alt')
INSERT INTO [dbo].[alt_code_ERP] ([UID],[item_id],[alternate_code_ERP]) VALUES (2, 'part2' , 'part2a_alt')
INSERT INTO [dbo].[alt_code_ERP] ([UID],[item_id],[alternate_code_ERP]) VALUES (3, 'part2' , 'part2b_alt')
INSERT INTO [dbo].[alt_code_ERP] ([UID],[item_id],[alternate_code_ERP]) VALUES (4, 'part2' , 'part2c_alt')
INSERT INTO [dbo].[alt_code_ERP] ([UID],[item_id],[alternate_code_ERP]) VALUES (5, 'part10' , 'part10_alt')
GO

INSERT INTO [dbo].[web_alt_code] ([UID],[item_id],[web_alternate_code]) VALUES (1, 'part1' , 'part1_alt')
INSERT INTO [dbo].[web_alt_code] ([UID],[item_id],[web_alternate_code]) VALUES (2, 'part2' , 'part2a_alt')
INSERT INTO [dbo].[web_alt_code] ([UID],[item_id],[web_alternate_code]) VALUES (3, 'part2' , 'part2d_alt')
INSERT INTO [dbo].[web_alt_code] ([UID],[item_id],[web_alternate_code]) VALUES (4, 'part8' , 'part8_alt')
INSERT INTO [dbo].[web_alt_code] ([UID],[item_id],[web_alternate_code]) VALUES (5, 'part10', 'part10b_alt')
GO

I want a result that will have the following:

  1. List each alternate code from each table once.
  2. If there are any matching alternate codes from the two tables, I want to align them side by side.
  3. Any without a match in the other table to be paired with a NULL

Something that looks like the following:

item_id alternate_code_erp web_alternate_code

part1       part1_alt   part1_alt 
part2       part2a_alt  part2a_alt
part2       part2b_alt  NULL
part2       part2c_alt  NULL
part2       NULL        part2d_alt
part8       NULL        part8_alt
part10      part10_alt  NULL
part10      NULL        part10b_alt

Best Answer

a full outer join on the item_id and alternate_code should do the trick.

    SELECT COALESCE([a].[item_id], [b].[item_id]) as item_id,
       [a].[alternate_code_ERP],
       [b].[web_alternate_code]
FROM [alt_code_ERP] AS [a]
     FULL OUTER JOIN [web_alt_code] AS [b]
     ON [a].[item_id] = [b].[item_id]
    AND [a].[alternate_code_ERP] = [b].[web_alternate_code];
Related Question