Avatar of Andy Donnelly
Andy Donnelly
Flag for United States of America asked on

Returning All Rows from one table and corresponding data from the other tables

SELECT        dbo.tblCCAllTransactionsSoft.FiscalYearCode, SUM(dbo.tblCCAllTransactionsSoft.DonorAmount) AS GiftAmount, CAST(dbo.tblCCAllTransactionsSoft.FYYear AS varchar) 
                         + ' - ' + CAST(dbo.tblCCAllTransactionsSoft.FYQuarter AS varchar) AS FYQTR, dbo.tblCCAllTransactionsSoft.FYYear, dbo.tblCCAllTransactionsSoft.FYQuarter, dbo.tblCCMetricAppealGroups.AppealGroup, 
                         dbo.tblAllPRMs.PRM FROM            dbo.tblCashTypes INNER JOIN                          dbo.tblCCAllTransactionsSoft ON dbo.tblCashTypes.TransType = dbo.tblCCAllTransactionsSoft.TransName INNER JOIN                          dbo.tblAllPRMs ON dbo.tblCCAllTransactionsSoft.DonorConstituentID = dbo.tblAllPRMs.ConstituentID RIGHT OUTER JOIN                          dbo.tblCCMetricAppealGroups ON dbo.tblCCAllTransactionsSoft.APPEAL_ID = dbo.tblCCMetricAppealGroups.AppealCode WHERE        (dbo.tblAllPRMs.SolicitorType = 10245) GROUP BY dbo.tblCCAllTransactionsSoft.FiscalYearCode, CAST(dbo.tblCCAllTransactionsSoft.FYYear AS varchar) + ' - ' + CAST(dbo.tblCCAllTransactionsSoft.FYQuarter AS varchar), dbo.tblCCAllTransactionsSoft.FYYear,                           dbo.tblCCAllTransactionsSoft.FYQuarter, dbo.tblCCMetricAppealGroups.AppealGroup, dbo.tblAllPRMs.PRM HAVING        (CAST(dbo.tblCCAllTransactionsSoft.FYYear AS varchar) + ' - ' + CAST(dbo.tblCCAllTransactionsSoft.FYQuarter AS varchar) = '2022 - 1') AND (dbo.tblAllPRMs.PRM = 'John Smith')

Open in new window

I'm need all of the AppealGroups in table tblCCMetricAppealGroups returned in the result.  Currently it is only returning rows where there is corresponding data in the other tables.  I thought that ON Right Outer join will do this but it is not working.

Microsoft SQL Server

Avatar of undefined
Last Comment

8/22/2022 - Mon
Ryan Chong

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

You are using "optional" columns in WHERE and HAVING. This converts the outer join into an inner join.
Move the WHERE condition into the outer join ON clause (or add   OR dbo.tblAllPRMs.SolicitorType IS NULL), and add IS NULL to the HAVING conditions.
But I would prefer Ryan's approach, which shows the logic much better.
Your help has saved me hundreds of hours of internet surfing.