By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 879 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

SQL Query Problem

P: 2
Hi:

I have a joined query from two tables. I want ro preserve ALL the data from the RIGHT table while summing the data from a field in the LEFT table having a specific criteria from a third field. Here's the code:

Expand|Select|Wrap|Line Numbers
  1. SELECT ActionType.[Action Type], Sum([6 Export OwnOcc Loan Activity].[Action Type]) AS [SumOfAction Type]
  2. FROM [6 Export OwnOcc Loan Activity] 
  3. RIGHT JOIN ActionType 
  4. ON [6 Export OwnOcc Loan Activity].[Action Type] = ActionType.[Action Type]
  5. GROUP BY ActionType.[Action Type], [6 Export OwnOcc Loan Activity].Occupancy
  6. HAVING ((([6 Export OwnOcc Loan Activity].Occupancy)="1"));

The query runs fine (showing all data from the "ActionType" table) when I remove the "HAVING ((([6 Export OwnOcc Loan Activity].Occupancy)="1"))" criteia. It does not show all the data from the "ActionType" table with that criteria in place.

Can someone tell me how to fix it to show ALL data from the "ActionType" table?
May 28 '12 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,357
Please use code tags when posting code.

That's because there's no record in the left table. So the occupancy field isn't going to have any data. So when you say it has to have "1" in there, it's not going to return since it's not "1". You'll want to return the "1"s or where it's null.
May 29 '12 #2

P: 2
Rabbit:

Thanks. I think that makes sense. The key issue here is that we need the occupancy to only be the "1" values. I was thinking about that after I posted and thought it might work to do a pre-query filtering out the "1s" then do the joined query without the criteria.

I bet that would work...
May 29 '12 #3

Rabbit
Expert Mod 10K+
P: 12,357
Returning ones and nulls is no different than what you're trying to accomplish with a second query.
May 29 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.