Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

left join not working

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
al4629740
Asked:
al4629740
  • 3
  • 2
1 Solution
 
Brian CroweCommented:
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
 
al4629740Author Commented:
Thank you
Can you explain to me why it gets negated?
0
 
Brian CroweCommented:
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
 
al4629740Author Commented:
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
 
Brian CroweCommented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now