Solved

left join not working

Posted on 2014-09-25
5
169 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

Title # Comments Views Activity
Stored Proc - Rewrite 42 59
Download ms sql express. 2 27
SQL Query 2 33
how to restore or keep sql2000  backups useful... 2 14
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

860 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