views:

51

answers:

3

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.

+1  A: 

Change your JOINs to LEFT JOIN and they'll include non-matching records as well.

David Hedlund
I tried that but it sadly doesn't work.
Kev
really? do you get an error, or just the same behavior as your current?
David Hedlund
It think it comes from the WHERE clause. See my answer ;)
Romain
The `WHERE` clause + the `LEFT JOIN` did the trick. +1
Kev
+1  A: 

You should use LEFT JOINs instead of JOINs, and also beware that when a LEFT JOIN doesn't find a matching row in the right table, it'll return NULLs for any field from that right table.

That means you issue comes from your WHERE clause that references dom.pointerto in a non-NULL-safe way, which will effectively void the benefits of the LEFT JOIN.

Romain
Ah, yes, so you probably want to specify `WHERE (dom.pointerto=0 OR dom.pointerto IS NULL) ...`
PP
Combination of PP's and Romain's answer got this working. Thanks folks for the quick work.
Kev
A: 

Try Using outer join for table tbldomains


SELECT  
    dev.DeviceName, Count(dom.DomainID) AS CountOfDomains  
FROM  
    tblDevices dev  
JOIN  
   tblIPNumbers ip ON dev.DeviceName = ip.ServerName  
outer 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) 
valli
Tip: add <pre><code> ... </code></pre> around your code to make it easier for others to read
PP