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:
- List each alternate code from each table once.
- If there are any matching alternate codes from the two tables, I want to align them side by side.
- 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