none
Is this a bug in MS Access Left Join? RRS feed

  • Question

  • With two tables - 

    Table1

    Table2

    and a query - qry_Test1:

    Which has the following results:

    Joining Table1 to qry_TestI would expect to get Unknown next to Mike and Anne. 

    Instead I get "Unknown" next to all:

    However if I send the results of qry_Test1 to a table, then left join the table, I get the correct results:

    Why should this happen?

    With Thanks - Rob

    Monday, September 16, 2019 4:56 PM

Answers

  • While it might seem counter-intuitive at first glance,  there is no logical inconsistency in the results you are getting.  A constant such as 'Unknown' is not an attribute of any row in any table in a query's FROM clause.  It's not really an attribute of anything, but it is convenient to think of it as an attribute of all rows in the result table.  When a LEFT OUTER JOIN returns rows from two tables, one of which is a result table, the constant in effect is an attribute of the result table of the JOIN, not of the table on the right of the JOIN, and hence is returned in all rows.

    When you create a base table from the first query's result table, the column into which the value of the constant is inserted is of course an attribute of the rows in the table, so, when you use this table on the right side of a LEFT OUTER JOIN the value will only be returned in those rows where there is a match.

    Relational theory is pretty weird when dealing with empty tuples, and gets even weirder when dealing with empty relations, and even weirder still when dealing with relations with no headings (degree zero).  You can see why we shouldn't think of relations as equivalent to tables, but relational theorists nevertheless call the two possible types of relations of degree zero TABLE_DUM and TABLE_DEE.  The former has no tuples; the latter has only one tuple, which is the maximum it can have.  This might all seem a bit abstract, but  when it's understood that, in the relation algebra, which forms the basis of the database relational model, TABLE_DEE plays the equivalent to the role of zero in everyday arithmetic, it can be seen just how important these concepts are.  Thankfully we don't need to worry about them most of the time, but they do underlie the sort of behaviour which you've experienced.

    Ken Sheridan, Stafford, England

    • Marked as answer by iotc2000 Monday, September 16, 2019 7:07 PM
    Monday, September 16, 2019 6:39 PM

All replies

  • Check if this what you are after

    SELECT Table1.ID, Nz([Result],"UnKnown") AS Expr1
    FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.fID;

    Monday, September 16, 2019 5:36 PM
  • Hi John yes certainly - taking your suggestion :

    SELECT Table1.BenID, Nz([Result],"UnKnown") AS Res
    FROM Table1 LEFT JOIN Table2 ON Table1.BenID = Table2.fkBenID

    Gives me correct results in a simplified manner. However, the example is for illustrative purposes only. There are many complex situations where I am expecting that type of left join to work properly. Why does this error occur?

    Monday, September 16, 2019 5:46 PM
  • While it might seem counter-intuitive at first glance,  there is no logical inconsistency in the results you are getting.  A constant such as 'Unknown' is not an attribute of any row in any table in a query's FROM clause.  It's not really an attribute of anything, but it is convenient to think of it as an attribute of all rows in the result table.  When a LEFT OUTER JOIN returns rows from two tables, one of which is a result table, the constant in effect is an attribute of the result table of the JOIN, not of the table on the right of the JOIN, and hence is returned in all rows.

    When you create a base table from the first query's result table, the column into which the value of the constant is inserted is of course an attribute of the rows in the table, so, when you use this table on the right side of a LEFT OUTER JOIN the value will only be returned in those rows where there is a match.

    Relational theory is pretty weird when dealing with empty tuples, and gets even weirder when dealing with empty relations, and even weirder still when dealing with relations with no headings (degree zero).  You can see why we shouldn't think of relations as equivalent to tables, but relational theorists nevertheless call the two possible types of relations of degree zero TABLE_DUM and TABLE_DEE.  The former has no tuples; the latter has only one tuple, which is the maximum it can have.  This might all seem a bit abstract, but  when it's understood that, in the relation algebra, which forms the basis of the database relational model, TABLE_DEE plays the equivalent to the role of zero in everyday arithmetic, it can be seen just how important these concepts are.  Thankfully we don't need to worry about them most of the time, but they do underlie the sort of behaviour which you've experienced.

    Ken Sheridan, Stafford, England

    • Marked as answer by iotc2000 Monday, September 16, 2019 7:07 PM
    Monday, September 16, 2019 6:39 PM
  • Hi Ken, this is a fantastic answer. I tested the reasoning (I hope I understood it fully) and removed the constant "Unknown". So qry_Test1 is a standard exclusion query:

    SELECT Table1.BenID FROM Table1 LEFT JOIN Table2 ON Table1.BenID = Table2.fkBenID
    WHERE (((Table2.TestID) Is Null));

    And the left join query is:

    SELECT Table1.BenID, Table1.FirstName, IIf(Not [qry_Test1].[BenID] Is Null, "Unknown",Null) AS Result

    FROM Table1 LEFT JOIN qry_Test1 ON Table1.BenID = qry_Test1.BenID;

    And the results were perfect.

    I must say I'm relieved because I use this type of query often - but without the constant column. What a gotcha!

    Many Thanks - Rob

    Monday, September 16, 2019 7:07 PM