My table structure is like this I want expected output as below,Pls help.
create table #table1 (id bigint)
create table #table2 (table1id bigint,tableid bigint,tableType varchar(20) )
create table #table3 (id bigint,name varchar(10))
insert #table1 select 1
insert #table1 select 2
insert #table1 select 3
insert #table2 select 1,1000,'table4'
insert #table2 select 1,1001,'table3'
insert #table2 select 2,1000,'table4'
insert #table2 select 3,200,'table5'
insert #table3 select 1001,'Peter'
select * from #table1 a inner join #table2 b
on a.ID = b.table1id
left join #table3 on #table3.id = b.tableid and tableType = 'table3'
select COUNT(*),name
from #table1 a inner join #table2 b
on a.ID = b.table1id
left join #table3 on #table3.id = b.tableid and tableType = 'table3'
group by name
expected result --group by table3
count name
1 Peter
2 Others