Try below query..............please Let me know if this helps.
with cte as (select t1.District,t1.Center,t3.s_No,t3.id,case isnull(t2.id,0) when 0 then 'Newly added' else 'Matched' end as status
from Table_2 t2 right outer join Table_3 t3 on t2.S_No = t3.s_No
inner join Table_1 t1 on t3.S_No = t1.s_No)
select t1.District,t1.Center,t1.s_No,isnull(cte.status,'Un_matched') as Status from Table_1 t1 left outer join cte on t1.s_No = cte.s_No