[SOLVED] Problematic JOIN, Need Non-Standard...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

[SOLVED] Problematic JOIN, Need Non-Standard...

Author

Topic

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2015-05-01 : 10:30:33

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:

I have 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(*) as 'Sessions'
  FROM 
      tb_assets
  LEFT JOIN 
      tb_sessions ss 
    ON 
      ss.f_computername = f_assetnetbiosname  
  --WHERE f_sessiondate BETWEEN '04/01/2015' AND '04/30/2015' 
  GROUP BY f_assetnetbiosname

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

The query based on these results would return the following result set:


|=================================|
| f_assetnetbiosname  |	Sessions  |
|=================================|
| COMP_001	      | 5         |
| COMP_002	      | 1         |
| COMP_003	      | NULL      |
|=================================|

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:


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

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 :).

Thanks in advance

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-05-01 : 11:10:37

Your WHERE clause will trun you LEFT JOIN into a JOIN.
(ie a f_seesiondate of NULL will be excluded.)

Your FROM clause should be:


FROM tb_assets A
    LEFT JOIN tb_sessions S
        ON A.f_assetnetbiosname = S.f_computername
            -- use ISO date strings etc
            AND S.f_sessiondate >= '20150401'
            AND S.f_sessiondate < '20150501'

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2015-05-01 : 11:10:38

I apologize - you know how it goes when you're troubleshooting your own problem so long that you can't see the simple solution?

The fix was to simply search for NULL values in addition to the date range. Here's the query that works:


  SELECT 
      f_assetnetbiosname, COUNT(*) as 'Sessions'
  FROM 
      tb_assets
  LEFT JOIN 
      tb_sessions ss 
    ON 
      ss.f_computername = f_assetnetbiosname  
  WHERE (f_sessiondate BETWEEN '04/01/2015' AND '04/30/2015'  OR f_sessiondate IS NULL)
  GROUP BY f_assetnetbiosname

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources