Solved

left join not working

Posted on 2014-09-25
5
166 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
ID: 40344560
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
ID: 40344735
Thank you
Can you explain to me why it gets negated?
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 40344849
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
ID: 40345036
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
ID: 40345068
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

831 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