CODEWITHSUNDEEP
sql-serverjoincount
Fhd.ashraf
Fhd.ashraf

Reputation: 537

Counting from 2 tables

I have 2 tables.

A

Id  Name
1    ab
2    cd
3    eg
4    fg

B

FkID 
1
2
2
1
1
2
4

Since 3 is not there in B in the FKID column. I need to find the count in table A which has value in table B which have a value in FKID also. So th total count should be 3. In my query, I am getting 7 after the left join.

Upvotes: 1

Views: 72

Answers (2)

Arulkumar
Arulkumar

Reputation: 13237

Using COUNT(DISTINCT B.FkId) the result can be achiveable. The COUNT is not considering the NULL values, so the following query will work.

SELECT COUNT(DISTINCT B.FkId) AS Occurence
FROM TableA A
LEFT JOIN TableB B ON B.FkId = A.Id;

or it can be achiveable with INNER JOIN too

SELECT COUNT(DISTINCT B.FkId) AS Occurence
FROM TableA A
INNER JOIN TableB B ON B.FkId = A.Id;

Demo with sample data:

DECLARE @TableA TABLE  (Id INT, [Name] VARCHAR (2));

INSERT INTO @TableA (Id, [Name]) VALUES
(1, 'ab'),
(2, 'cd'),
(3, 'eg'),
(4, 'fg');

DECLARE @TableB TABLE (FkId INT);

INSERT INTO @TableB (FkId) VALUES
(1),
(2),
(2),
(1),
(1),
(2),
(4);

SELECT COUNT(DISTINCT B.FkId) AS Occurence
FROM @TableA A
LEFT JOIN @TableB B ON B.FkId = A.Id

Upvotes: 2

forpas
forpas

Reputation: 164089

You don't need a join, you can do it with EXISTS:

select count(*) from a
where exists (
  select 1 from b
  where b.fkid = a.id
)

I believe EXISTS is more efficient than a join, but if you need a join then it has to be an INNER JOIN like this:

select count(distinct a.id)
from a inner join b
on b.fkid = a.id

Upvotes: 3

Related Questions