with nonOrphan_MFRs as ( select mfr_id from tblManufacturers where coalesce(fseProductCatalogContactId,0) > 0 ) select distinct clientName, ownerid, ownerType, ownerType + '|' + cast(ownerid as varchar) as OwnerTypeOwnerId , case when nonOrphan_MFRs.mfr_id is not null then 'Assigned MFR' else 'Unassigned MFR' end as MFR_Status from tbl_GS1_BatchMessages with (nolock) left outer join nonOrphan_MFRs on mfr_id = ownerid where 1 = 1 and createDate between and and messageCode = and ownerId NOT IN () and ownerId in () and mfr_id > 0 and coalesce(mfr_id,0) = 0 order by clientName