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;