Finding the count from 2 different tables-CodePudding

I have 3 tables - 2 Input and 1 Lookup

Tab1
Id 
4
4
5
6
8
2

Tab2
ID
4
4
1
1
2
6

tab3
ID   Name
1    One
2    two
3    three
4    four
5    five
6    six
7    seven
8    eight

I need a query that will help me get

Count Name
1     One
1     two
0     three
1     four
1     five
1     six
0     seven
1     eigh

I have tried the In etc but I am not able to get the result. Also, the Full join counts the ID twice instead of once.

SELECT 
   COUNT(DISTINCT a.id)   COUNT(DISTINCT b.id) AS Total, 
   c.id 
FROM c 
INNER JOIN a ON a.id= b.id 
LEFT OUTER JOIN b ON c.id = b.id 
GROUP BY c.id

Ive tried this but I am not getting the desired result.

CodePudding user response：

If I understand the question correctly, a possible solution is the following statement using an UNION (to excludes duplicates from the input tables) and the appropriate JOIN using the lookup table:

Tables:

SELECT Id INTO Tbl1
FROM (VALUES (4), (4), (5), (6), (8), (2)) v (Id)

SELECT Id INTO Tbl2
FROM (VALUES (4), (4), (1), (1), (2), (6)) v (Id)

SELECT Id, Name
INTO tbl3
FROM (VALUES
   (1, 'One'), (2, 'two'), (3, 'three'), (4, 'four'), (5, 'five'), (6, 'six'), (7, 'seven'), (8, 'eight')
) v (Id, Name)

Statement:

SELECT t.Name, Count = COUNT(j.Id)
FROM tbl3 t
FULL JOIN (
   SELECT Id FROM tbl1
   UNION
   SELECT Id FROM tbl2
) j ON t.Id = j.Id
GROUP BY t.Name