Try this:

if object_id('dbo.MasterDefects') is not null

drop table dbo.MasterDefects;

if object_id('dbo.Results') is not null

drop table dbo.Results;

create table dbo.MasterDefects

(

Department Varchar(20),

Process Varchar(20),

Type Varchar(20),

Defect Varchar(20)

);

insert dbo.MasterDefects values ( 'Timber', 'CrossCut', 'Wood Fault', 'Fallen Out Knot' );

insert dbo.MasterDefects values ( 'Timber', 'CrossCut', 'Wood Fault', 'Resin Pocket' );

insert dbo.MasterDefects values ( 'Timber', 'CrossCut', 'Wood Fault', 'Large Knot' );

insert dbo.MasterDefects values ( 'Timber', 'CrossCut', 'Wood Fault', 'Wet Timber' );

insert dbo.MasterDefects values ( 'Timber', 'CrossCut', 'Wood Fault', 'Wood Rott' );

insert dbo.MasterDefects values ( 'Timber', 'CrossCut', 'Wood Fault', 'Blue Stain' );

insert dbo.MasterDefects values ( 'Timber', 'CrossCut', 'Wood Fault', 'Chipped Off' );

create table dbo.Results

(

Department Varchar(20),

Process Varchar(20),

Type Varchar(20),

Defect Varchar(20),

ShiftID Varchar(20),

SizeID Varchar(20),

Amount int,

ShiftBox Varchar(20)

);

insert dbo.Results values ( 'Timber', 'CrossCut', 'Wood Fault', 'Fallen Out Knot', '1ae', '1des', 1, 'RTO1' );

insert dbo.Results values ( 'Timber', 'CrossCut', 'Wood Fault', 'Fallen Out Knot', '1ae', '1des', 1, 'RTO1' );

insert dbo.Results values ( 'Timber', 'CrossCut', 'Wood Fault', 'Fallen Out Knot', '1ae', '1des', 1, 'RTO1' );

insert dbo.Results values ( 'Timber', 'CrossCut', 'Wood Fault', 'Resin Pocket', '1ae', '1des', 1, 'RTO1' );

insert dbo.Results values ( 'Timber', 'CrossCut', 'Wood Fault', 'Resin Pocket', '1ae', '1des', 1, 'RTO1' );

insert dbo.Results values ( 'Timber', 'CrossCut', 'Wood Fault', 'Large Knot', '1ae', '1des', 1, 'RTO1' );

SELECT

M.Department,

M.Process,

M.Type,

M.Defect,

R.ShiftID,

R.SizeID,

SUM(R.Amount) as Amount,

R.ShiftBox

FROM MasterDefects M

LEFT OUTER JOIN Results R

ON M.Department=R.Department

AND M.Process= R.Process

AND M.Type=R.Type

AND M.Defect=R.Defect

GROUP BY

M.Department,

M.Process,

M.Type,

M.Defect,

R.ShiftID,

R.SizeID,

R.ShiftBox;

Edit: Corrected. Results not in the same order - DK if that matters.

If you supply test data in a format that can be run, more people will be likely to help! 😀