Filter Count Clause – One to Many relationship?
I currently have two tables where a version log can contain many ProductVersions.
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
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?
Manda Ben
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;