SQLTeam.com | Weblogs | Forums

Query Help


#1

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


#2

So are you asking how to get the sum for "Others"?


#3

Just saw this is a duplicate topic of this