Solved

left join not working

Posted on 2014-09-25
5
155 Views
Last Modified: 2014-09-25
I am trying to display all the Organizations in my table with the Sum of those who are 7 and under years of Age.  here is the following query:

DECLARE @fromdt AS datetime 
DECLARE @todt AS datetime
SET @fromdt = '7/1/2014'
SET @todt = '7/31/2014'
select r.Agency, count(Distinct r.regid) as Count from tblOrgRegistrations as r 
LEFT JOIN tblOrgHours as h
 ON h.RegID = r.regid
 Where (h.ActivityDate >= @fromdt And h.ActivityDate <= @todt) And r.AgeRegistration <= 7
 Group By r.Agency order by r.Agency

Open in new window


Output is:

Agency      Count
Administrator      5
Bishop Shepard      2
Cicero Area Project      4
Harvey Brooks      18
Major Adams      8
Mid-Austin      5
Scouting Network      31
SELF      12
Westside Community      4

The problem is that not every Organization is showing up.  Only the ones with values are showing up.  If they have 0, then I still want it to come up and be 0 or NULL

How can I do this?
0
Comment
Question by:al4629740
  • 3
  • 2
5 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
Comment Utility
Your LEFT JOIN is negated by your WHERE clause.  Requiring h.ActivityDate to fall within @fromdt and @todt removes any NULL values created by the LEFT JOIN.

Move those parameters to the join.

SELECT r.Agency, COUNT(Distinct r.regid) as [Count]
FROM tblOrgRegistrations as r 
LEFT JOIN tblOrgHours as h
	ON h.RegID = r.regid
	AND h.ActivityDate >= @fromdt
	AND h.ActivityDate <= @todt
WHERE r.AgeRegistration <= 7
GROUP BY r.Agency
ORDER BY r.Agency

Open in new window

0
 

Author Comment

by:al4629740
Comment Utility
Thank you
Can you explain to me why it gets negated?
0
 
LVL 34

Expert Comment

by:Brian Crowe
Comment Utility
You have to envision the result set from the join.  Given the following tables:

TableA:
ID
1
2
3
4
5

TableB:
ID     TableA_ID
1      1
2      3
3      4

Open in new window


SELECT A.ID AS A_ID, B.ID AS B_ID, B.TableA_ID
FROM TableA AS A
LEFT OUTER JOIN TableB AS B
   ON A.ID = B.TableA_ID

Open in new window


returns the following:

A_ID     B_ID     TableA_ID
1        1        1
2        NULL     NULL
3        2        3
4        3        4
5        NULL     NULL

Open in new window


But if you add a WHERE Clause to the same query that puts a restriction on TableB like "WHERE B.TableA_ID IN (3,4)" then you would only get back those two rows since NULL is NOT IN (3,4).  The WHERE Clause is enforced after the joined set is created.

Hope that makes sense
0
 

Author Comment

by:al4629740
Comment Utility
So the where clause is based on the first results which is:

SELECT r.Agency, COUNT(Distinct r.regid) as [Count]
FROM tblOrgRegistrations as r
LEFT JOIN tblOrgHours as h
      ON h.RegID = r.regid
      AND h.ActivityDate >= @fromdt
      AND h.ActivityDate <= @todt
0
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 500 total points
Comment Utility
Yes, using the query with the date conditions enforced in the JOIN instead of the WHERE your result set would have one or more records for each record in tblOrgRegistrations but only a single record with NULL values for the tblOrgHours fields.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now