Talk About Network

Google


Register and Login
Nick
Password
Register create new account Sign up is FREE and you can post replies, new topics, bookmark posts and more!
Recover lost password


Software > Access Queries > LEFT JOIN shoul...
Latest [ Topics | Posts ] Archive Post A New Topic Post a Reply
<< Topic < Post Post 1 of 10 Topic 41153 of 43104
Post > Topic >>

LEFT JOIN shouldn't it work like this...?

by =?Utf-8?B?VmVyeSBSdXN0eSBVc2Vy?= <VeryRustyUser@[EMAIL PROTECTED] Aug 4, 2008 at 02:51 PM

I'm using Access 2007.

I have 3 tables.  The first is Invoices the second is Payments and the
third 
is PaymentAssignments (I’ll call it “PA” – it’s an intersection
table between 
the Invoices table and the Payments table).

I want my query to combine fields from both Invoices and PA and to return 
ALL records from Invoices where the fields from PA are null (they are null

only if there is no matching record).  (But I’m not trying to only get 
Invoice records with no matching PA records – I want ALL Invoice records

regardless of whether there are any matching PA records).

So I LEFT JOIN Invoices to PA and add criteria “WHERE PA.Field1 is
null.”
But the problem is then I ONLY  get Invoice records that have NO matching 
record in PA.

With NO where clause, shouldn’t I get BOTH an Invoice record where the
field 
from PA is null AND all Invoice records where the field from PA is not
null?  
I’m only getting one Invoice record (the one where the field from PA is
not 
null).  
Take Invoice #100, for example.  If Invoice #100 has a matching record in
PA 
then my query with NO WHERE CLAUSE returns one record for Invoice #100.  
Shouldn’t it return 2 records for Invoice #100 (one where PA.field1 is
null 
and one where PA.field1 is not null)?

What am I missing?  Your help is appreciated, thanks!
 




 10 Posts in Topic:
LEFT JOIN shouldn't it work like this...?
=?Utf-8?B?VmVyeSBSdXN0eSB  2008-08-04 14:51:03 
Re: LEFT JOIN shouldn't it work like this...?
Lord Kelvan <the_iddio  2008-08-04 16:48:29 
Re: LEFT JOIN shouldn't it work like this...?
=?Utf-8?B?VmVyeSBSdXN0eSB  2008-08-04 17:13:00 
Re: LEFT JOIN shouldn't it work like this...?
"david" <dav  2008-08-05 11:32:03 
Re: LEFT JOIN shouldn't it work like this...?
=?Utf-8?B?VmVyeSBSdXN0eSB  2008-08-04 19:28:00 
RE: LEFT JOIN shouldn't it work like this...?
=?Utf-8?B?S2VuIFNoZXJpZGF  2008-08-04 16:54:03 
RE: LEFT JOIN shouldn't it work like this...?
=?Utf-8?B?VmVyeSBSdXN0eSB  2008-08-04 17:25:00 
Re: LEFT JOIN shouldn't it work like this...?
Lord Kelvan <the_iddio  2008-08-04 18:36:23 
Re: LEFT JOIN shouldn't it work like this...?
=?Utf-8?B?VmVyeSBSdXN0eSB  2008-08-04 20:58:06 
Re: LEFT JOIN shouldn't it work like this...?
Lord Kelvan <the_iddio  2008-08-04 21:04:40 

Post A Reply:
  Go here to Signup

AddThis Feed Button


About - Advertising - Contact - Frequently Asked Questions - Privacy Policy - Terms of Use - Signup

Contact
tan12V112 Thu Nov 20 22:17:23 CST 2008.