CODEWITHSUNDEEP
sql-serversql-server-2008
Beems
Beems

Reputation: 810

SQL JOIN not showing NULL results when WHERE clause used

Server: SQL Server 2008 R2
SQL Fiddle: http://sqlfiddle.com/#!6/112a6/4/0

I apologize for the poor subject/title, but I'm having a hard time verbalizing how to state my problem, so I'll draw it out with a (probably) lengthy example:

Below is a rather simple query that gets the number of sessions for a PC for a certain date range. Note at this point that the WHERE clause is commented out. What I'm hoping to receive is the full list of PCs, even those that have ZERO/NULL sessions during that range. However, when I add that WHERE clause I ONLY get the PCs that had sessions during that range. This is problematic because I can't see the PCs that were unused during that period. 

SELECT 
    f_assetnetbiosname, 
    COUNT(f_sessiondate) as 'Sessions'
FROM 
    tb_assets ass
LEFT JOIN 
    tb_sessions ses
  ON 
    ses.f_sessionnetbiosname = ass.f_assetnetbiosname  
--WHERE (f_sessiondate BETWEEN '04/01/2015' AND '04/30/2015')
GROUP BY f_assetnetbiosname

========================   =======================================|
| tb_assets            |   | tb_sessions                          |
========================   =======================================|
|   f_assetnetbiosname |   |  f_sessionnetbiosname  f_sessiondate |
------------------------   ---------------------------------------|
|     COMP_001         |   |    COMP_002            03/29/2015    |
|     COMP_002         |   |    COMP_002            03/30/2015    |
|     COMP_003         |   |    COMP_001            03/30/2015    |
------------------------   |    COMP_001            04/02/2015    |
                           |    COMP_001            04/03/2015    |
                           |    COMP_001            04/04/2015    |
                           |    COMP_001            04/05/2015    |
                           -----------------------------------

The query based on these results returns the following result set:

|=================================|
| f_assetnetbiosname  | Sessions  |
|=================================|
| COMP_001            | 5         |
| COMP_002            | 2         |
| COMP_003            | 0         |
|=================================|

The problem is that I need to actually search only for a date range. So if I uncomment out the "WHERE" clause in the query above, the result set looks only like the following:

|=================================|
| f_assetnetbiosname  | Sessions  |
|=================================|
| COMP_001            | 4         |
|=================================|

But I need it to look like the following so that I can see the PCs that had zero sessions:

|=================================|
| f_assetnetbiosname  | Sessions  |
|=================================|
| COMP_001            | 4         |
| COMP_002            | 0         |
| COMP_003            | 0         |
|=================================|

Does anybody have a fix for this? Note that the example above is just that - an example. The real query is a multi-CTE bearcat but I figured it better to whittle this down to the basics so that I don't cloud the conversation by showing off my bad code.

SQL Fiddle: http://sqlfiddle.com/#!6/112a6/4/0

Thanks in advance,
Beems

Upvotes: 1

Views: 91

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460058

You could add it to the Join clause:

SELECT 
    f_assetnetbiosname, 
    COUNT(f_sessiondate) as 'Sessions'
FROM 
    tb_assets ass
LEFT OUTER JOIN 
    tb_sessions ses
  ON 
    ses.f_sessionnetbiosname = ass.f_assetnetbiosname 
    AND f_sessiondate BETWEEN '04/01/2015' AND '04/30/2015'
GROUP BY f_assetnetbiosname

Here's a demo: http://sqlfiddle.com/#!6/112a6/23/0

That's the difference of a Join-clause and a Where which is also mentioned here.

Upvotes: 5

SLin
SLin

Reputation: 375

It sounds like you only need to count based on a date so you can put the condition into your count.

SELECT f_assetnetbiosname, 
COUNT(CASE WHEN (f_sessiondate BETWEEN '04/01/2015' AND '04/30/2015') 
           THEN 1 ELSE null
           END) as 'Sessions'
FROM tb_assets ass
LEFT JOIN tb_sessions ses ON ses.f_sessionnetbiosname = ass.f_assetnetbiosname  
GROUP BY f_assetnetbiosname

http://sqlfiddle.com/#!6/112a6/27

Upvotes: 0

Related Questions