Count of 0 in Left Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Count of 0 in Left Join

Author

Topic

yanaho
Starting Member

20 Posts

Posted - 2010-03-31 : 10:29:36

Hi I am trying to query two tables, Tenants and Certificates.
I would like to retrieve the count of Tenants having no Certificates.

Join Fields are TenantID in TenantTable, and _TenantID in Certificates Table.

Any help would be appreciated.

Thanks!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-31 : 10:33:25

select count(t.tenantID)
from Tenants t left join certificates c
on t. TenantID = c. _TenantID
where c._TenantID is null

Harsh Athalye
http://www.letsgeek.net/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-31 : 10:38:17

Is there any issue to query like this -


Create table Tenants ( TenantID int, col1 varchar(10) )
Create table Certificates( _TenantID int, col1 varchar(10) )

insert into Tenants Values(1, 'aaa')

SELECT COUNT(t.TenantID), Count(c._TenantID) FROM Tenants t left join Certificates c on t.TenantID = c._TenantID

Vaibhav T

yanaho
Starting Member

20 Posts

Posted - 2010-03-31 : 10:40:06

Thank you. Should have know it was that easy.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-31 : 10:43:11

quote:

Originally posted by vaibhavktiwari83

Is there any issue to query like this -


Create table Tenants ( TenantID int, col1 varchar(10) )
Create table Certificates( _TenantID int, col1 varchar(10) )

insert into Tenants Values(1, 'aaa')

SELECT COUNT(t.TenantID), Count(c._TenantID) FROM Tenants t left join Certificates c on t.TenantID = c._TenantID

Vaibhav T

In this case, it would return count of tenants having certificates as well as count of tenants not having certificates. Also, if one tenant has multiple certificates, count(c._tenantID) will add that too giving wrong result.

Harsh Athalye
http://www.letsgeek.net/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources