John,

Here is simplification of the 2 tables.

They are matched on one key item.

#input1 - row 5 has only one match.

#input1 - row 6 has no matches.

++++++

CREATE TABLE [dbo].[#Input1](

[RECORD_ID] [numeric](10, 0) not NULL,

[keyItem] varchar(15) not null )

insert into #Input1

values ( '1', '113456')

insert into #Input1

values ( '2','113457')

insert into #Input1

values ( '3','113458')

insert into #Input1

values ( '4','113459')

insert into #Input1

values ( '5','113460')

insert into #Input1

values ( '6','113461')

CREATE TABLE [dbo].[#Input2](

[input2_ID] [numeric](10, 0) not NULL,

[Item] varchar(15) NULL,

[I_desc] varchar(24) null,

[S_desc] varchar(50) null,

[M_desc] varchar(10) null,

[KeyItem] varchar(15) not null )

insert into #Input2

values ('1', '123','desc','blue','rg','113456')

insert into #Input2

values ( '2','123','desc','blue','rg','113457')

insert into #Input2

values ( '3','123','desc','blue','rg','113458')

insert into #Input2

values ( '4','123','desc2','blue','rg','113459')

insert into #Input2

values ('5', '123','desc','blue','rg','113456')

insert into #Input2

values ( '6','123','desc','blue','rg','113456')

insert into #Input2

values ( '7','123','desc','blue','rg','113457')

insert into #Input2

values ( '8','123','desc','blue','rg','113457')

insert into #Input2

values ( '7','123','desc','blue','rg','113458')

insert into #Input2

values ( '8','123','desc','blue','rg',',113458')

insert into #Input2

values ( '9','123','desc','blue2','rg','113459')

insert into #Input2

values ( '10','123','desc','blue3','rg','113459')

insert into #Input2

values ( '11','123','desc','blue3','rg','113459')

insert into #Input2

values ( '12','123','desc','blue3','rg','113460')

Select Record_Id, #input2.keyItem, item, i_desc, S_desc, M_desc, ' ' as status

from #input1 Join #input2

on #input1.KeyItem = #input2.KeyItem

order by record_Id

drop table #input1

drop table #input2