Query help

Transact-SQL
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

you didn't explain the required logic.

Just my guess .. .

select COUNT(DISTINCT tableType), isnull(name, 'Others')
from #table1 a
inner join #table2 b on a.ID = b.table1ID
left join #table3 c on c.ID = b.tableID and b.tableType = 'table3'
group by name

3

Sorry for not explaining much in my previous post.

#table1 is a parent table and #table2 is reference table which has one to many relation.

I want to group data based on #table1. If any value matches data from #table3 it should be considered as one group and unmatched data apart from matched group should be considered as others

Pls find the below example.

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 #table1 select 4
insert #table1 select 5
insert #table1 select 6
insert #table1 select 7

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 #table2 select 4,1002,'table3'
insert #table2 select 5,1002,'table3'
insert #table2 select 5,1000,'table4'
insert #table2 select 5,200,'table5'
insert #table2 select 6,1001,'table3'
insert #table2 select 7,1000,'table4'
insert #table2 select 7,1001,'table3'

Count Name

2 Others
2 Hein
3 Peter

4

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

5 
SELECT ISNULL(name, 'Others') AS name, COUNT(*) AS [count]
FROM (
    SELECT
        b.table1id,
        MAX(c.name) AS name,
        COUNT(*) AS [count]
    FROM #table2 b
    LEFT OUTER JOIN #table3 c
        ON c.id = b.tableid
    GROUP BY b.table1id
) AS subquery1
GROUP BY name
ORDER BY name
6

Thanks ScottPletcher..Its working as expected.