Left Join Works With Table But Fails With Query

The following left join query in MS Access 2007 SELECT Table1.Field_A, Table1.Field_B, qry_Table2_Combined.Field_A, qry_Table2_Combined.Field_B, qry_Table2_Combined.Combined_Field

Solution 1:

Looking at you logic, it seems that you only want combined fields where field_A = "2" (SELECT '2' AS Field_A). I suspect that this is causing the problem. Would it be possible to go about a solution in a different way, for example:

SELECT 
   t1.Field_A, 
   t1.Field_B, 
   t2.Field_B As t2B, 
   [t2].[Col_1] & ", " & [t2].[Col_2] AS Combined
FROM t1 LEFT JOIN t2 
ON t1.Field_B = t2.Field_B
WHERE t1.Field_A="2"
UNION ALL
SELECT  
   t1.Field_A, 
   t1.Field_B, 
   "None"As t2B, 
   "None"AS Combined
FROM t1 
WHERE t1.Field_A<>"2"

Solution 2:

Concatenation: change the & operators to + operators and the result should be as expected.

---

Missing rows: I can reproduce this issue but cannot explain it, other than to say a) it's probably a bug and b) it will probably never get fixed :(

For sanity I tested the same code in SQL Server and it works as expected.

As a general point an outer join can be simulated using union and padding the missing values e.g. pseudo code:

( A JOIN B )
UNION
( A NOT MATCH B { A.*, <pad values for B> } )

In your case and in Access SQL:

SELECT Table1.Field_A, Table1.Field_B,  
       qry_Table2_Combined.Field_A,  
       qry_Table2_Combined.Field_B,  
       qry_Table2_Combined.Combined_Field  
  FROM Table1  
       INNERJOIN qry_Table2_Combined  
          ON (Table1.Field_A = qry_Table2_Combined.Field_A)  
             AND (Table1.Field_B = qry_Table2_Combined.Field_B)
UNIONALLSELECT Table1.Field_A, Table1.Field_B,  
       NULLAS Field_A,  
       NULLAS Field_B,  
       NULLAS Combined_Field  
  FROM Table1  
 WHERENOTEXISTS ( SELECT*FROM qry_Table2_Combined 
                     WHERE (Table1.Field_A = qry_Table2_Combined.Field_A)  
                            AND (Table1.Field_B = qry_Table2_Combined.Field_B) );

The above seems to produce the results you were expecting.

---

Access repro code, with concatenation fix, uncomment code for suggested workaround:

Sub EXfewfTempler()

  OnErrorResumeNext
  Kill Environ$("temp") & "\DropMe.mdb"OnErrorGoTo0Dim cat
  Set cat = CreateObject("ADOX.Catalog")

  With cat
    .Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\DropMe.mdb"With .ActiveConnection

      Dim Sql AsString

      Sql = "CREATE TABLE Table1 (  Field_A VARCHAR(10),  Field_B VARCHAR(10) );"
      .Execute Sql

      Sql = "CREATE TABLE Table2 ( Field_B VARCHAR(10),  Col_1 VARCHAR(10),  Col_2 VARCHAR(10));"
      .Execute Sql

      Sql = "CREATE VIEW qry_Table2_Combined AS SELECT '2' AS Field_A, Table2.Field_B, Table2.Col_1 + ', ' + Table2.Col_2 AS Combined_Field FROM Table2; "
      .Execute Sql

      Sql = "INSERT INTO Table1 VALUES (1, NULL);"
      .Execute Sql
      Sql = "INSERT INTO Table1 VALUES (1, NULL);"
      .Execute Sql
      Sql = "INSERT INTO Table1 VALUES (2, 1);"
      .Execute Sql
      Sql = "INSERT INTO Table1 VALUES (2, 2);"
      .Execute Sql
      Sql = "INSERT INTO Table2 VALUES (1, 'John', 'Doe');"
      .Execute Sql

      Sql = _
          "SELECT " & _
          "Table1.Field_A, " & _
          "Table1.Field_B, " & _
          "qry_Table2_Combined.Field_A, " & _
          "qry_Table2_Combined.Field_B, " & _
          "qry_Table2_Combined.Combined_Field " & _
          "FROM Table1 " & _
          "LEFT JOIN qry_Table2_Combined " & _
          " ON (Table1.Field_A = qry_Table2_Combined.Field_A) " & _
          "AND (Table1.Field_B = qry_Table2_Combined.Field_B);"'      Sql = _'          "SELECT Table1.Field_A, Table1.Field_B, " & _'          "       qry_Table2_Combined.Field_A, " & _'          "       qry_Table2_Combined.Field_B, " & _'          "       qry_Table2_Combined.Combined_Field " & _'          "  FROM Table1 " & _'          "       INNER JOIN qry_Table2_Combined " & _'          "          ON (Table1.Field_A = qry_Table2_Combined.Field_A) " & _'          "             AND (Table1.Field_B = qry_Table2_Combined.Field_B) " & _'          "UNION ALL " & _'          "SELECT Table1.Field_A, Table1.Field_B, " & _'          "       NULL AS Field_A, " & _'          "       NULL AS Field_B, " & _'          "       NULL AS Combined_Field " & _'          "  FROM Table1 " & _'          " WHERE NOT EXISTS ( SELECT * " & _'          "                      FROM qry_Table2_Combined " & _'          "                     WHERE (Table1.Field_A = qry_Table2_Combined.Field_A) " & _'          "                            AND (Table1.Field_B = qry_Table2_Combined.Field_B) );"Dim rs
      Set rs = .Execute(Sql)
      MsgBox rs.GetString(2, , vbTab & vbTab, , "<NULL>")

    EndWithSet .ActiveConnection = NothingEndWithEndSub

Solution 3:

isn't this a problem with MSAccess parsing. for a test change the field names in the query to Field_C and Field_D and see if you still have the same problem