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

[Very Rusty User]

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!

 

[Lord Kelvan]

can you paste the actual query for us to look at it may help us find
the problem but you may want to use the right join rather than the
left but thats only a guess

 

[Ken Sheridan]

When a row in the Invoices table has no matching row in the PA table when
LEFT OUTER JOINED to it a reference to a column from the PA table will
evaluate to NULL. If there is a matching row or rows it will evaluate to NOT
NULL. It can't evaluate to both NULL and NOT NULL simultaneously; that's a
logical impossibility.

So when the query is not restricted to WHERE PA.Field1 IS NULL returning the
one row for Invoice #100 is to be expected if there is one matching row in
PA. If there were two matching rows for Invoice #100 in PA then the query
would return two rows. For invoices with no matching rows in PA it will
return 1 row per unmatched invoice. Which is what you want, surely?

You could force a result set of the type you describe by means of a UNION
ALL operation, but I can't see that it would serve any purpose.

Ken Sheridan
Stafford, England

 

[Very Rusty User]

Thanks for working on this. Here is the query.

SELECT Invoice.InvoiceID, Invoice.InvoiceDate AS [Inv Date],
Invoice.InvoiceTotal AS [Inv Total], Invoice.PrepaidAmount AS Prepaid,
Invoice.TotalPaymentsAssigned AS [Prior Payments], Invoice.AmountForgiven AS
Forgiven,
Format(NZ([InvoiceTotal])-NZ([PrepaidAmount])-NZ([TotalPaymentsAssigned]),"Currency")
AS Due, InvoicePaymentAssign.AmountApplied AS [Pay Now],
InvoicePaymentAssign.InvoicePaymentAssignID, Invoice.CustomerID,
InvoicePaymentAssign.PaymentID
FROM Invoice LEFT JOIN InvoicePaymentAssign ON Invoice.InvoiceID =
InvoicePaymentAssign.InvoiceID
WHERE (((InvoicePaymentAssign.InvoicePaymentAssignID) Is Null))
ORDER BY Invoice.InvoiceID;

 

[Very Rusty User]

When a row in the Invoices table has no matching row in the PA table when
LEFT OUTER JOINED to it a reference to a column from the PA table will
evaluate to NULL. If there is a matching row or rows it will evaluate to NOT
NULL. It can't evaluate to both NULL and NOT NULL simultaneously; that's a
logical impossibility.

Thanks for helping me with this. I still don't understand why I can't get
both records (the one where the field is NULL and the other one where the
field is NOT NULL). I'm only asking for the one where the field is NULL. It
will only give it to me if a NOT NULL one doesn't exist.) Can you try to
explain it another way so that I can understand? I'm not understanding why
it would need to evaluate to both NULL and NOT NULL simultaneously. Am I
really asking it to do that? Once again, THANKS!

So when the query is not restricted to WHERE PA.Field1 IS NULL returning the
one row for Invoice #100 is to be expected if there is one matching row in
PA. If there were two matching rows for Invoice #100 in PA then the query
would return two rows. For invoices with no matching rows in PA it will
return 1 row per unmatched invoice. Which is what you want, surely?

You could force a result set of the type you describe by means of a UNION
ALL operation, but I can't see that it would serve any purpose.

Is a UNION query updateable? I'm using this query in an input form to
assign payments to invoices so it needs to be updateable. I thought about a
UNION query but I still can't figure out how to get the LEFT side of it (I
mean get all the Invoice records regardless of whether there is a matching
record in PA. Because I need to join to the PA table to get those fields I
want and that's my original problem. How do I join to it and get a truly
LEFT truly OUTER join?)

Thanks!

 

[david]

The WHERE clause is evaluated first. Try

WHERE (
((InvoicePaymentAssign.InvoicePaymentAssignID) Is Null)
or
((InvoicePaymentAssign.InvoiceID) Is Null)
)


(david)

Thanks for working on this. Here is the query.

SELECT Invoice.InvoiceID, Invoice.InvoiceDate AS [Inv Date],
Invoice.InvoiceTotal AS [Inv Total], Invoice.PrepaidAmount AS Prepaid,
Invoice.TotalPaymentsAssigned AS [Prior Payments], Invoice.AmountForgiven
AS
Forgiven,
Format(NZ([InvoiceTotal])-NZ([PrepaidAmount])-NZ([TotalPaymentsAssigned]),"Currency")
AS Due, InvoicePaymentAssign.AmountApplied AS [Pay Now],
InvoicePaymentAssign.InvoicePaymentAssignID, Invoice.CustomerID,
InvoicePaymentAssign.PaymentID
FROM Invoice LEFT JOIN InvoicePaymentAssign ON Invoice.InvoiceID =
InvoicePaymentAssign.InvoiceID
WHERE (((InvoicePaymentAssign.InvoicePaymentAssignID) Is Null))
ORDER BY Invoice.InvoiceID;

can you paste the actual query for us to look at it may help us find
the problem but you may want to use the right join rather than the
left but thats only a guess

 

[Lord Kelvan]

Thanks for working on this.  Here is the query.

SELECT Invoice.InvoiceID, Invoice.InvoiceDate AS [Inv Date],
Invoice.InvoiceTotal AS [Inv Total], Invoice.PrepaidAmount AS Prepaid,
Invoice.TotalPaymentsAssigned AS [Prior Payments], Invoice.AmountForgivenAS
Forgiven,
Format(NZ([InvoiceTotal])-NZ([PrepaidAmount])-NZ([TotalPaymentsAssigned]),"­Currency")
AS Due, InvoicePaymentAssign.AmountApplied AS [Pay Now],
InvoicePaymentAssign.InvoicePaymentAssignID, Invoice.CustomerID,
InvoicePaymentAssign.PaymentID
FROM Invoice LEFT JOIN InvoicePaymentAssign ON Invoice.InvoiceID =
InvoicePaymentAssign.InvoiceID
WHERE (((InvoicePaymentAssign.InvoicePaymentAssignID) Is Null))
ORDER BY Invoice.InvoiceID;

left join wont work like that if you are using a condition like that i
found that problem as well. remove the where statement and you should
get what you are looking for but you may not need the join i look at
that and i see

(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).

if you are trying to do that just remove the where statement and the
left join because in that query you are not selecting any data from
the other table and then that will give you all invoice records but if
you are wanting to add information form the payment table you have to
use a left and a right join but remove the where condition

because you are only doing your left join on your associtive entity it
wont work what you need to do is use all three table and left join
from invoice to invoicepaymentassign and right join from payment to
invoicepaymentassign and it should do what you want if not swap the
joins around

 

[Very Rusty User]

Thanks David but that gave the same results as the original query. Thanks
anyway

The WHERE clause is evaluated first. Try

WHERE (
((InvoicePaymentAssign.InvoicePaymentAssignID) Is Null)
or
((InvoicePaymentAssign.InvoiceID) Is Null)
)


(david)

Thanks for working on this. Here is the query.

SELECT Invoice.InvoiceID, Invoice.InvoiceDate AS [Inv Date],
Invoice.InvoiceTotal AS [Inv Total], Invoice.PrepaidAmount AS Prepaid,
Invoice.TotalPaymentsAssigned AS [Prior Payments], Invoice.AmountForgiven
AS
Forgiven,
Format(NZ([InvoiceTotal])-NZ([PrepaidAmount])-NZ([TotalPaymentsAssigned]),"Currency")
AS Due, InvoicePaymentAssign.AmountApplied AS [Pay Now],
InvoicePaymentAssign.InvoicePaymentAssignID, Invoice.CustomerID,
InvoicePaymentAssign.PaymentID
FROM Invoice LEFT JOIN InvoicePaymentAssign ON Invoice.InvoiceID =
InvoicePaymentAssign.InvoiceID
WHERE (((InvoicePaymentAssign.InvoicePaymentAssignID) Is Null))
ORDER BY Invoice.InvoiceID;

can you paste the actual query for us to look at it may help us find
the problem but you may want to use the right join rather than the
left but thats only a guess

 

[Very Rusty User]

Thanks for working on this. Here is the query.

SELECT Invoice.InvoiceID, Invoice.InvoiceDate AS [Inv Date],
Invoice.InvoiceTotal AS [Inv Total], Invoice.PrepaidAmount AS Prepaid,
Invoice.TotalPaymentsAssigned AS [Prior Payments], Invoice.AmountForgiven AS
Forgiven,
Format(NZ([InvoiceTotal])-NZ([PrepaidAmount])-NZ([TotalPaymentsAssigned]),"­Currency")
AS Due, InvoicePaymentAssign.AmountApplied AS [Pay Now],
InvoicePaymentAssign.InvoicePaymentAssignID, Invoice.CustomerID,
InvoicePaymentAssign.PaymentID
FROM Invoice LEFT JOIN InvoicePaymentAssign ON Invoice.InvoiceID =
InvoicePaymentAssign.InvoiceID
WHERE (((InvoicePaymentAssign.InvoicePaymentAssignID) Is Null))
ORDER BY Invoice.InvoiceID;

left join wont work like that if you are using a condition like that i
found that problem as well. remove the where statement and you should
get what you are looking for but you may not need the join i look at
that and i see

(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).

if you are trying to do that just remove the where statement and the
left join because in that query you are not selecting any data from
the other table and then that will give you all invoice records but if
you are wanting to add information form the payment table you have to
use a left and a right join but remove the where condition

It's this that worked:

because you are only doing your left join on your associtive entity it
wont work what you need to do is use all three table and left join
from invoice to invoicepaymentassign and right join from payment to
invoicepaymentassign and it should do what you want if not swap the
joins around

I joined Payment and InvoicePaymentAssign in one query and put the WHERE
clause in it to actually keep it from returning any records. Then I LEFT
JOINED the Invoice table to that query in a new query. The new query gave me
all Invoice records (whether or not there was a matching record in
InvoicePaymentAssign) and it let me have the fields I wanted from the
InvoicePaymentAssign table in the query results. Exactly what I wanted.

Thank you SO VERY much. I appreciate your help and I admire your brain.
Thanks to everyone who responded to my plea for help

 

[Lord Kelvan]

no problem