select m.mfr_id, m.mfr_name from tblManufacturers m where exists ( select g.groupId from tbl_SEC_Groups g where g.ownerId = m.mfr_Id and g.fsl_tablecode = 'MFR' and g.internalNotes like 'register%' and g.internalNotes like '%@' + m.emailDomain + ' %' and exists ( select * from tbl_SEC_AttrbGrpLink agl inner join tbl_SEC_Attributes a on a.attrbId = agl.attrbId and a.ownerId = g.ownerId and a.fsl_tablecode = g.fsl_tablecode and a.attrbTypeId = 3 and agl.groupId= g.groupId ) )