asked on

Query Syntax - Left Join not showing all groups with 0 entries

I thought I knew how to do this after my last post, but apparently not. I have the following code which works fine, however, the output needs to list groups from the profile table that have no entries at all. So, when I run the code currently, I get all the groups that have registered individuals. However, there may be some groups that have no registered individuals and they should show up as 0, however they don't.

With TOTAL_REGISTERED as 
(select r.regdate, r.Agency
FROM   tblOrgProfile p 

LEFT JOIN tblOrgRegistrations r
ON p.AgencyID = r.AgencyID
and r.fiscal = 2020

 where active = 1 and
 r.agency <> 'Administrator')

select Agency, 
SUM(CASE when regdate >= '7/1/2019' And regdate < '10/01/2019' then 1 end) as [1st Quarter],
SUM(CASE when regdate >= '10/01/2019' And regdate < '01/01/2019' then 1 ELSE 0 end) as [2nd Quarter],
SUM(CASE when regdate >= '01/01/2020' And regdate < '04/01/2020' then 1 ELSE 0 end) as [3rd Quarter],
SUM(CASE when regdate >= '04/01/2020' And regdate < '07/01/2020' then 1 ELSE 0 end) as [4th Quarter]
from TOTAL_REGISTERED T group by Agency order by agency

Open in new window

Please note that the tables attached do not reflect exactly all the data in the actual tables.
tblOrgProfile.xlsx
tblOrgRegistrations.xlsx

Scott Pletcher

You have to move all comparisons to columns in the left table to the OUTER JOIN:

With TOTAL_REGISTERED as
(select r.regdate, r.Agency
FROM tblOrgProfile p

LEFT JOIN tblOrgRegistrations r
ON p.AgencyID = r.AgencyID
and r.fiscal = 2020 AND r.agency <> 'Administrator'

where p.active = 1 )
...

Dale Fye

or try this:

With TOTAL_REGISTERED as
(select r.regdate, r.Agency
FROM   tblOrgProfile p
LEFT JOIN (
SELECT * FROM tblOrgRegistrations
WHERE agency <> 'Administrator' and fiscal = 2020
) as r
ON p.AgencyID = r.AgencyID
where p.active = 1

Open in new window

al4629740

ASKER

Neither solution above worked. There should be 31 groups on the output, but only 27 showed up.

Scott Pletcher

Is the "active" column in the "p" table? You didn't alias most of the columns.

If the LEFT JOINed table does not have a matching row, ALL columns from that table will have a NULL value, including the r.Agency column. That is what you are grouping by in the final query. You need to use a column from the P table, for example AgencyId, in the final query so the column value isn't null.

al4629740

ASKER

I hope I'm understanding right. AgencyID is in both tables and that's the column referenced. Also Active is in the "p" table.

The main problem is that all the agency's that are marked active = 1 are not showing up particularly when the count is 0

Dale Fye

first step is to run this query and see what you get. This is from the subquery and will show you which records would have values for R.Redgate and R.Agency in your select statement.

SELECT * FROM tblOrgRegistrations
WHERE agency <> 'Administrator' and fiscal = 2020

Open in new window

But that causes me to question why your select statement doesn't contain any records from P. Theoretically, you could have a bunch of records displayed with NULLs in both R.Redgate and R.Agency, when using this left join.

Can you explain (in words) what you really want to do. Explain what the two tables contain and why you think you need to do a left join between P and the records selected in the sql posted above.

al4629740

ASKER

So if you look at my original question, essentially, I am trying to show all the groups that are active in the tblOrgProfile table regardless if they have registered individuals or not. Then, I want to put the total amount of registered individuals next to each group. Obviously, some may have no records at all so then they would have a value of 0.

ASKER CERTIFIED SOLUTION

al4629740

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial