Why doesn't my LEFT JOIN work?

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I have the following query that uses a LEFT JOIN:

SELECT Investment.Investment,
Sum(IIf(IsNull([qr_Amount.Amount]),0,qr_Amount.Amount))
FROM Investment LEFT JOIN qr_Amount ON Investment.InvestID =
qr_Amount.InvestID
GROUP BY Investment.Investment

With this query I get back all of my investment categories reqardless of
whether a particular category has an amount associated with it in the
right-side table (qr_Amount, which is actually a query) as in the example
below:

Stocks 10
Mutual Funds 20
Insurance 0
Bonds 0
MMF 10

However, if I include a WHERE clause that operates on a field in the
right-side table, I do not get a record returned for each row in the
left-side table.

For example, this query....

SELECT Investment.Investment,
Sum(IIf(IsNull([qr_Amount.Amount]),0,qr_Amount.Amount))
FROM Investment LEFT JOIN qr_Amount ON Investment.InvestID =
qr_Amount.InvestID
WHERE qr_Amount.CheckDate BETWEEN #6/12/04# and #6/12/04#
GROUP BY Investment.Investment

....returns only this:

Stocks 10
Mutual Funds 20
MMF 10

Why is this?

I thought the function of a LEFT JOIN was to return all records in the
left-side table regardless of whether there was a match in the right-side
table? The WHERE clause operates only on the right-side table so why do I
lose my left-side table records without matches?
 
Dave,

I think the key to the problem is in your statement "the WHERE clause
operates only on the right-side table". This is not correct. The WHERE
clause operates on the whole query. In the case of your example, the
records related to Insurance and Bonds do not have a CheckDate within
the date range you specified... in fact the CheckDate in these instances
will be Null. So you need to cater to this in your query, for example...

SELECT Investment.Investment, Sum(Nz([qr_Amount.Amount],0))
FROM Investment LEFT JOIN qr_Amount ON Investment.InvestID =
qr_Amount.InvestID
WHERE qr_Amount.CheckDate BETWEEN #6/12/04# and #6/12/04# Or
qr_Amount.CheckDate Is Null
GROUP BY Investment.Investment

(Notice I have also simplified your calculated field by the use of the
Nz() function.)
 
Steve:

Thanks for taking the time to clear that up for me.

Dave


Steve Schapel said:
Dave,

I think the key to the problem is in your statement "the WHERE clause
operates only on the right-side table". This is not correct. The WHERE
clause operates on the whole query. In the case of your example, the
records related to Insurance and Bonds do not have a CheckDate within
the date range you specified... in fact the CheckDate in these instances
will be Null. So you need to cater to this in your query, for example...

SELECT Investment.Investment, Sum(Nz([qr_Amount.Amount],0))
FROM Investment LEFT JOIN qr_Amount ON Investment.InvestID =
qr_Amount.InvestID
WHERE qr_Amount.CheckDate BETWEEN #6/12/04# and #6/12/04# Or
qr_Amount.CheckDate Is Null
GROUP BY Investment.Investment

(Notice I have also simplified your calculated field by the use of the
Nz() function.)

--
Steve Schapel, Microsoft Access MVP
I have the following query that uses a LEFT JOIN:

SELECT Investment.Investment,
Sum(IIf(IsNull([qr_Amount.Amount]),0,qr_Amount.Amount))
FROM Investment LEFT JOIN qr_Amount ON Investment.InvestID =
qr_Amount.InvestID
GROUP BY Investment.Investment

With this query I get back all of my investment categories reqardless of
whether a particular category has an amount associated with it in the
right-side table (qr_Amount, which is actually a query) as in the example
below:

Stocks 10
Mutual Funds 20
Insurance 0
Bonds 0
MMF 10

However, if I include a WHERE clause that operates on a field in the
right-side table, I do not get a record returned for each row in the
left-side table.

For example, this query....

SELECT Investment.Investment,
Sum(IIf(IsNull([qr_Amount.Amount]),0,qr_Amount.Amount))
FROM Investment LEFT JOIN qr_Amount ON Investment.InvestID =
qr_Amount.InvestID
WHERE qr_Amount.CheckDate BETWEEN #6/12/04# and #6/12/04#
GROUP BY Investment.Investment

...returns only this:

Stocks 10
Mutual Funds 20
MMF 10

Why is this?

I thought the function of a LEFT JOIN was to return all records in the
left-side table regardless of whether there was a match in the right-side
table? The WHERE clause operates only on the right-side table so why do I
lose my left-side table records without matches?
Click to expand...
Click to expand...
 
Dave,

Another method would be to do a subquery on qr_Amount and then join it to
Investment. Something like:

SELECT I.Investment, SUM(NZ(Q.Amount, 0)) as Something
FROM Investment I
LEFT JOIN
(SELECT Q1.InvestID, Q1.Amount
FROM qr_Amount Q1
WHERE Q1.CheckDate BETWEEN #6/12/04# and #6/12/04#) as Q
ON I.InvestID = Q.InvestID
GROUP BY I.Investment

I prefer this method over the method (personal preference only) Steve posted
because it is easier for me to understand what is going on in the query. I
can look at the sub-query and tell exactly what I am trying to do.

HTH
Dale
 
It's odd enough. Why not use the codes as below:

SELECT Investment.Investment, SUM(Nz([qr_Amount.Amount],0))
FROM Investment
LEFT OUTER JOIN qr_Amount
ON Investment.InvestID = qr_Amount.InvestID
AND qr_Amount.CheckDate BETWEEN #6/12/04# and #6/12/04#
OR qr_Amount.CheckDate Is Null
GROUP BY Investment.Investment

Using the above codes, you can put anything on where statement for left
table(s).

Dale Fye said:
Dave,

Another method would be to do a subquery on qr_Amount and then join it to
Investment. Something like:

SELECT I.Investment, SUM(NZ(Q.Amount, 0)) as Something
FROM Investment I
LEFT JOIN
(SELECT Q1.InvestID, Q1.Amount
FROM qr_Amount Q1
WHERE Q1.CheckDate BETWEEN #6/12/04# and #6/12/04#) as Q
ON I.InvestID = Q.InvestID
GROUP BY I.Investment

I prefer this method over the method (personal preference only) Steve posted
because it is easier for me to understand what is going on in the query. I
can look at the sub-query and tell exactly what I am trying to do.

HTH
Dale

Dave said:
I have the following query that uses a LEFT JOIN:

SELECT Investment.Investment,
Sum(IIf(IsNull([qr_Amount.Amount]),0,qr_Amount.Amount))
FROM Investment LEFT JOIN qr_Amount ON Investment.InvestID =
qr_Amount.InvestID
GROUP BY Investment.Investment

With this query I get back all of my investment categories reqardless of
whether a particular category has an amount associated with it in the
right-side table (qr_Amount, which is actually a query) as in the example
below:

Stocks 10
Mutual Funds 20
Insurance 0
Bonds 0
MMF 10

However, if I include a WHERE clause that operates on a field in the
right-side table, I do not get a record returned for each row in the
left-side table.

For example, this query....

SELECT Investment.Investment,
Sum(IIf(IsNull([qr_Amount.Amount]),0,qr_Amount.Amount))
FROM Investment LEFT JOIN qr_Amount ON Investment.InvestID =
qr_Amount.InvestID
WHERE qr_Amount.CheckDate BETWEEN #6/12/04# and #6/12/04#
GROUP BY Investment.Investment

...returns only this:

Stocks 10
Mutual Funds 20
MMF 10

Why is this?

I thought the function of a LEFT JOIN was to return all records in the
left-side table regardless of whether there was a match in the right-side
table? The WHERE clause operates only on the right-side table so why
Click to expand...
do
I
lose my left-side table records without matches?
Click to expand...
Click to expand...
 
Personal preference.

It's been several years, and I don't have time to look for my notes, but I
recall having done some analysis on the two queries, and found that my
method seemed to be a little quicker. I think it has to do with the
sequence that JET uses to process the query.

Dale

IPT said:
It's odd enough. Why not use the codes as below:

SELECT Investment.Investment, SUM(Nz([qr_Amount.Amount],0))
FROM Investment
LEFT OUTER JOIN qr_Amount
ON Investment.InvestID = qr_Amount.InvestID
AND qr_Amount.CheckDate BETWEEN #6/12/04# and #6/12/04#
OR qr_Amount.CheckDate Is Null
GROUP BY Investment.Investment

Using the above codes, you can put anything on where statement for left
table(s).

Dale Fye said:
Dave,

Another method would be to do a subquery on qr_Amount and then join it to
Investment. Something like:

SELECT I.Investment, SUM(NZ(Q.Amount, 0)) as Something
FROM Investment I
LEFT JOIN
(SELECT Q1.InvestID, Q1.Amount
FROM qr_Amount Q1
WHERE Q1.CheckDate BETWEEN #6/12/04# and #6/12/04#) as Q
ON I.InvestID = Q.InvestID
GROUP BY I.Investment

I prefer this method over the method (personal preference only) Steve posted
because it is easier for me to understand what is going on in the query. I
can look at the sub-query and tell exactly what I am trying to do.

HTH
Dale

Dave said:
I have the following query that uses a LEFT JOIN:

SELECT Investment.Investment,
Sum(IIf(IsNull([qr_Amount.Amount]),0,qr_Amount.Amount))
FROM Investment LEFT JOIN qr_Amount ON Investment.InvestID =
qr_Amount.InvestID
GROUP BY Investment.Investment

With this query I get back all of my investment categories reqardless of
whether a particular category has an amount associated with it in the
right-side table (qr_Amount, which is actually a query) as in the example
below:

Stocks 10
Mutual Funds 20
Insurance 0
Bonds 0
MMF 10

However, if I include a WHERE clause that operates on a field in the
right-side table, I do not get a record returned for each row in the
left-side table.

For example, this query....

SELECT Investment.Investment,
Sum(IIf(IsNull([qr_Amount.Amount]),0,qr_Amount.Amount))
FROM Investment LEFT JOIN qr_Amount ON Investment.InvestID =
qr_Amount.InvestID
WHERE qr_Amount.CheckDate BETWEEN #6/12/04# and #6/12/04#
GROUP BY Investment.Investment

...returns only this:

Stocks 10
Mutual Funds 20
MMF 10

Why is this?

I thought the function of a LEFT JOIN was to return all records in the
left-side table regardless of whether there was a match in the right-side
table? The WHERE clause operates only on the right-side table so why
Click to expand...
do
I
lose my left-side table records without matches?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top