Cacher is the code snippet organizer for pro developers

--Если поле cc.Responsible не равно null то выводится оно, иначе один
CASE WHEN cc.Responsible is not null THEN cc.Responsible ELSE 1 END  as Responsible


select 
        c.Id , 
        cc.Id , 
        rtrim(ltrim(e.EmailString)) as EmailString,
        CASE WHEN cc.Responsible is not null THEN cc.Responsible ELSE 1 END  as Responsible
            from [ELMA].[dbo].Email e
                left join [ELMA].[dbo].Contractor_Email ce on ( ce.Email = e.Id )
                left join [ELMA].[dbo].Contact_Email cce on ( cce.Email = e.Id )
                left join [ELMA].[dbo].Contact cc on ( cc.Id = cce.Contact and cc.IsDeleted = 0 )
                left join [ELMA].[dbo].Contractor c on ( c.Id = ce.Contractor and c.IsDeleted = 0 )
                left join [ELMA].[dbo].Contractor c_cc on ( c_cc.Id = cc.Contractor and c_cc.IsDeleted = 0 and cc.IsDeleted = 0 )
                where e.EmailString is not null and LEN(e.EmailString) > 0 and (cc.Id is not null or c.Id is not null)