MS Access Left Join not working correctly

Question

I am trying to write a query in MS Access 2013 and the left join is not working correctly. It is acting like a normal join.

Here is what I'm trying to do.

My first table [All Category Types] has one column [Category Types]. I am then trying to left join that to a query that has two aggregate fields in it. Virtual Table [Average by Category Type] is first grouped by Owner, and then by [Category Type]. Next is a sum field [CountOfIncident: Number].

What I want as a result is every item in table [All Category Types] and then the correct [CountOfIncident: Number] where Owner == "France". This is not working as a left join. It is only showing me the values in [All Category Types] that have a matching record in [Average by Category Type].

If I remove Owner from this table, and only group by [Category Type], it works just fine. Is there something about having more than one field in the group by clause that does not allow a left join on a query to work correctly?

SELECT [All Category Types].[Category Type], 
       [Average by Category Type].[CountOfIncident: Number]
FROM [All Category Types] 
LEFT JOIN [Average by Category Type] 
ON [All Category Types].[Category Type] = [Average by Category Type].[Category Type]
WHERE ((([Average by Category Type].Owner)="France"));

Thank you.

My 5c: I find queries less readable when the columns contain blanks and must be escaped. Why not simply `SELECT all_category_types.category_type`? I also find queries less readable when multiple tables are involved, but no table aliases used (e.g. `SELECT ct.category_type`). I also find queries less readable that have a lot of superfluous parentheses. Why not simply `WHERE [Average by Category Type].Owner = "France"`? At last I'd prefer to use standard SQL wherever possible. That would be single quotes for string literals: `= 'France'`. — Thorsten Kettner, Sep 12 '17 at 21:30
Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Oct 09 '20 at 23:36
@philipxy That duplicate is MySQL, and while the cause is the same, Access requires parentheses when joining with a constant, which is a common cause for confusion and that's discussed here as well. I think this question is sufficiently distinct. — Erik A, Oct 12 '20 at 13:24
@ErikA That question has been asked again & again & again. Did you google for it under Access? Moreover it doesn't depend on any DBMS-specific aspects of SQL. The explanations make that clear by appealing to the properties of the operators. So it's a duplicate. — philipxy, Oct 12 '20 at 23:49

Thorsten Kettner · Answer 1 · 2017-09-15T05:54:14.823

3

[Average by Category Type].Owner = "France" can only be true for inner joined records. For outer joined records [Average by Category Type].Owner is null.

So your WHERE clause turns your outer join into an inner join. Move the condition to the ON clause:

SELECT 
  [All Category Types].[Category Type], 
  [Average by Category Type].[CountOfIncident: Number]
FROM [All Category Types] 
LEFT JOIN [Average by Category Type] 
  ON ([Average by Category Type].[Category Type] = [All Category Types].[Category Type]
  AND [Average by Category Type].Owner = "France");

UPDATE: Unlike other DBMS MS Access needs parentheses for the ON clause: JOIN tablename ON (...) rather than JOIN tablename ON ....

edited Sep 15 '17 at 05:54

answered Sep 12 '17 at 21:16

Thorsten Kettner

89,309
7
49
73

Was just about to propose the same. You can change that `WHERE` statement to `WHERE ((([Average by Category Type].Owner)="France")) OR [Average by Category Type].Owner IS NULL` – Erik A Sep 12 '17 at 21:17
@Erik von Asmuth: It's better to move the condition to the `ON` clause where it belongs. I've updated my answer accordingly. – Thorsten Kettner Sep 12 '17 at 21:18
Not for MS Access. It tends to run into trouble with such comparisons in the `ON` clause (for starters, the query builder won't work anymore, but had queries fail on me when I did that (JOIN expression not supported, Access is picky in its joins)). For other RDBMSes you're right. – Erik A Sep 12 '17 at 21:20
Again and again, I am sooo glad, I don't have to work with MS Access. I had to many years ago and it was horrible due to all its limitations. And from what I read it still seems to be about the worst DBMS on the market. – Thorsten Kettner Sep 12 '17 at 21:25
See https://stackoverflow.com/questions/16608313/join-expression-not-supported-in-access for a question that does a join like you intend to do, and fails, because you can't do that in Access. – Erik A Sep 12 '17 at 21:29
MS Access is nasty when it comes to joins, I know. Most often it's about putting many seemingly superfluous parentheses where Access wants to have them :-( In the link's query that may be a cause. Another may be the missing alias name for the derived table. – Thorsten Kettner Sep 12 '17 at 21:36
After all of the fuss about formatting and extra parenthesis, the lone parenthesis in the last expression is screaming to be removed. – C Perkins Sep 15 '17 at 05:15
@ErikvonAsmuth The suggested WHERE clause will not work since it still excludes rows where this is match but Owner is not 'France'. Although the join in Thorsten's answer won't work in Access, it at least would produce the correct result if Access did support the join. In the end, none of these suggestions actually answer the question correctly. – C Perkins Sep 15 '17 at 05:27
@CPerkins I can easily modify my approach to do the same as the join, but the asker hasn't responded, and I'm not 100% sure that's what he wants – Erik A Sep 15 '17 at 05:41
@ErikvonAsmuth Definitely it's best if the asker responds. But the last few statements imply that he wants what Thorsten's answer would give. Also in this case, the other linked SO answer is also not immediately helpful since it deals only with INNER JOINS. In this case the asker needs a subquery (or to edit the [Average by Category Type] query) to filter on Owner before the LEFT JOIN. I was going to submit an alternative answer unless you already had that in mind. – C Perkins Sep 15 '17 at 05:51
@C Perkins: Thanks for mentioning the parenthesis I had too may. I tried my query in MS Access now and it shows that Access needs parentheses around the `ON` criteria. I've updated my answer. The corrected query works properly in my tests. – Thorsten Kettner Sep 15 '17 at 05:53
@ThorstenKettner I hate Access. If your query really works then I give up. I thought I had just about figured out what kind of join expressions Access allows and I was certain it would not accept literals like that. I almost wanted to defend Access earlier, but perhaps you're right about how bad it is. I just wish there was another DBMS that would do the few things Access does well. – C Perkins Sep 15 '17 at 06:00

C Perkins · Answer 2 · 2017-09-15T06:27:44.740

1

Thorsten Kettner's statement is correct that the WHERE clause has essentially turned the statement into the equivalent of an INNER JOIN.

The key to making the LEFT JOIN work is understanding the order in which criteria are applied. JOIN criteria are always applied first, then the WHERE clause is applied after all records are joined. Since in a LEFT JOIN you want all records from the left table regardless of whether the join condition is satisfied, you must ensure that all conditions are applied either BEFORE or as part of the join condition.

If you can get Thorsten's answer to work with all conditions in the join, I suggest that approach. But in case you have problems (highly likely with Access) then you can also apply some of the conditions in a subquery or by adding WHERE criteria within the saved query [Average by Category Type]. Perhaps it is obvious but in context of the above explanation regarding order of execution, the subquery --including any filtering in the WHERE clause-- is completed before the LEFT JOIN is applied.

SELECT 
  [All Category Types].[Category Type], 
  AverageFiltered.[CountOfIncident: Number]
FROM [All Category Types] 
  LEFT JOIN 
    (SELECT * FROM [Average by Category Type] 
     WHERE [Average by Category Type].Owner = 'France')
    As AverageFiltered
  ON [All Category Types].[Category Type] = AverageFiltered.[Category Type];

edited Sep 15 '17 at 06:27

answered Sep 15 '17 at 06:10

C Perkins

3,733
4
23
37

I could actually get the most recent edit of Thorsten Kettner's answer to work on a sample database. Perhaps remove the part about his question. – Erik A Sep 15 '17 at 09:32
@ErikvonAsmuth Well, in this exact case I also got it to work. But even your discussion with Thorsten revealed that it can often be difficult with Access to get the parenthesis correct. In the last month I have had to completely rework two queries because no combination of parenthesis would fix certain join conditions. Since these answers are not just for the original asker's benefit, I think a reference to the other answer is justified. I'm giving him due credit and upvoted his answer, but this alternative could be critical in other cases. – C Perkins Sep 15 '17 at 13:40
Note that you can always go the horrible way: `ON [Average by Category Type].[Category Type] = [All Category Types].[Category Type] AND [Average by Category Type].Owner = Right([All Category Types].[Category Type] & "France", 6)` works fine without any extra parentheses – Erik A Sep 15 '17 at 14:15
That's the trick I had used in the past... I thought it was required to reference one of the tables on both sides of the logic operator, but apparently not always. My problem earlier was a join using the Between operator with dates. It was working great with no problems, but then I added just _one more column_ to my query from an existing table already in the query, I think another date value... with no name conflicts, no grouping problems, nothing. Suddenly I was getting duplicate alias errors and unsupported join errors. Nothing worked but to use basic joins with subqueries. – C Perkins Sep 15 '17 at 15:54

MS Access Left Join not working correctly

2 Answers2

Linked