I have the following query:
SELECT
dev.DeviceName, Count(dom.DomainID) AS CountOfDomains
FROM
tblDevices dev
JOIN
tblIPNumbers ip ON dev.DeviceName = ip.ServerName
JOIN
tblDomains dom ON dom.IPNumberID = ip.IPNumberID
WHERE
dom.PointerTo=0
AND dev.DeviceType='3'
AND (dev.[System]='32' OR dev.[System]='33')
AND dom.ClosedDate IS NULL AND dev.Active=1
GROUP BY
dev.DeviceName
ORDER BY
Count(dom.DomainID)
The tables look like:
tblDomains ========== DomainID int IPNumberID int ClosedDate datetime PointerTo int tblIPNumbers ============ IPNumberID int ServerName varchar(200) tblDevices ========== DeviceID int DeviceName varchar(200) System varchar(10) DeviceType varchar(10) Active bit
Sample Data:
tblDomains: =========== DomainID: 1234 IPNumberID: 1000 ClosedDate: NULL PointerTo: 0 tblIPNumbers: ============= IPNumberID: 1000 ServerName: WIN2008-01 tblDevices: =========== DeviceID: 1 DeviceName: WIN2008-01 System: 32 Active: 1 DeviceType: 3
The problem is that if there are no rows in tblDomains
that match an IPNumberID
in tblIPNumbers
I get no rows returned. I'd like the query to return a single row of 0
for Count(dom.DomainID) AS CountOfDomains
in this case.
I've tried various combinations of LEFT
and RIGHT
joins and it seems like a simple problem but my SQL-fu is low today.