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

Why aren't all records included in query?

Seth Schrock
Expert 2.5K+
P: 2,951
I've got a query that is supposed to return all the records associated with the current CustomerID, however if one of the fields is blank (the Status field to be exact), then the record is not included. Here is the SQL code:
Expand|Select|Wrap|Line Numbers
  1. SELECT [Returned Mail].[Customer Name], 
  2. [Returned Mail].SituationID, 
  3. [Returned Mail].[Date Mail Returned], 
  4. Status.Status, 
  5. [Returned Mail].SelectThisMail
  6. FROM Status INNER JOIN [Returned Mail] ON Status.[Status ID] = [Returned Mail].Status
  7. WHERE ((([Returned Mail].[Customer Name])=[Forms]![Customer]![CustomerID]))
  8. ORDER BY [Returned Mail].[Date Mail Returned];
  9.  
I'm kind of wondering if it is because of the join on that field. If that is the case, is there a way to include those records where that field is blank?

P.S.
I know that the naming is bad. This was my first database and I didn't know about naming conventions at the time. I have since learned the value of proper naming techniques.
Dec 21 '11 #1

✓ answered by Rabbit

That's because you've inner joined on status which means it will only return a record if there is a match on status. You'll want to use a left join instead.

Share this Question
Share on Google+
6 Replies


Rabbit
Expert Mod 10K+
P: 12,421
That's because you've inner joined on status which means it will only return a record if there is a match on status. You'll want to use a left join instead.
Dec 21 '11 #2

Seth Schrock
Expert 2.5K+
P: 2,951
As it turns out, I needed the right join, but it was you who pointed me in the correct direction. Thanks so much.
Dec 21 '11 #3

NeoPa
Expert Mod 15k+
P: 31,707
An OUTER JOIN then. Both LEFT and RIGHT JOINs are OUTER JOINs.
Dec 22 '11 #4

Seth Schrock
Expert 2.5K+
P: 2,951
For educational purposes, is there a reason to choose OUTER JOIN instead of LEFT or RIGHT JOINs? Or is it just simpler to not have to figure out which side you need?
Dec 22 '11 #5

NeoPa
Expert Mod 15k+
P: 31,707
I wasn't being clear. There is a LEFT OUTER JOIN and a RIGHT OUTER JOIN. Jet supports only these, and they can each be written more briefly as LEFT JOIN and RIGHT JOIN respectively. T-SQL (and presumably others) support a balanced (Full) OUTER JOIN. To see more, and what gives what, visit SQL JOINs.

I wasn't trying to correct Rabbit's comment as there was nothing to correct. Simply give some background for better understanding.
Dec 22 '11 #6

Seth Schrock
Expert 2.5K+
P: 2,951
Okay. Thanks for the link.
Dec 22 '11 #7

Post your reply

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