CODEWITHSUNDEEP
sqlsql-server
user3116167
user3116167

Reputation: 579

Filter Count Clause - One to Many relationship

I currently have two table where a versionLog can contain many ProductVersions.

Following Sql Query:

SELECT
versionlog.[Name],
(
    SELECT COUNT(*) 
    FROM dbo.ProductVersions productVersion 
    WHERE productVersion.VersionLogId = versionLog.Id  
) AS ProductVersions
FROM dbo.Versionlog versionLog

produces a result set similar to

Name | ProductVersions
Log1  | 12
Log2  |  6
Log3  |  0

etc..

How can I limit the result set to only return Versionlogs with a ProductVersion Count = 0?

According to google I'll need to use the Having clause?

Upvotes: 1

Views: 213

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

another way subquery

select * from 
(
SELECT
versionlog.[Name],
(
    SELECT COUNT(*) 
    FROM dbo.ProductVersions productVersion 
    WHERE productVersion.VersionLogId = versionLog.Id  
) AS ProductVersions
FROM dbo.Versionlog versionLog
) a where a.ProductVersions=0

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175884

Using HAVING:

SELECT versionlog.[Name]
FROM dbo.Versionlog versionLog
LEFT JOIN dbo.ProductVersions productVersion 
  ON productVersion.VersionLogId = versionLog.Id  
GROUP BY versionlog.[Name]
HAVING COUNT(productVersion.VersionLogId) = 0;

And without grouping:

SELECT versionlog.[Name]
FROM dbo.Versionlog versionLog
LEFT JOIN dbo.ProductVersions productVersion 
  ON productVersion.VersionLogId = versionLog.Id  
WHERE productVersion.VersionLogId IS NULL

Upvotes: 1

Related Questions