select
c.operator,
c.unit,
c.unitcompleted,
GR_Operator,
GR_Date,
HS_Operator,
HS_Date,
HA_Operator,
HA_Date,
case when oa.SN is not null then 'Scanned' else 'Not Scanned' end as Inspected
from
dbo.completedUnit c
OUTER APPLY(select s.qcOperator, s.grFPDate, s.haOperator, s.hadateFP, s.hsOperator, s.dateinspectedFP, s.SN from dbo.assemblyQC s where s.SN = c.SN) oa (GR_Operator,
GR_Date,
HA_Operator,
HA_Date,
HS_Operator,
HS_Date,
SN)