I've tried this query.I want to know whether any other solution is better than this because the involved tables are heavy in production.
I want to avoid the redundant reference.
; WITH CTE AS (
select table1ID ,name
from #table1 a
inner join #table2 b on a.ID = b.table1ID
left join #table3 c on c.ID = b.tableID
where name is not null
),cte1 as (select CTE.,a.id
from #table1 a
inner join #table2 b on a.ID = b.table1ID
left join #table3 c on c.ID = b.tableID left join cte on cte.table1ID = a.id where cte.table1id is null )
SELECT COUNT(),name from CTE group by name
union all
SELECT COUNT(*),name FROM CTE1 group by name