LEFT JOINing on additional criteria in MS Access

left-join, ms-access, sql

Solution

You need to use a subselect to apply the condition:

  SELECT *
  FROM dbo_A LEFT JOIN 
    [SELECT dbo_B.* FROM dbo_B WHERE dbo_B.F_ID = 3]. AS dbo_B 
      ON dbo_A.ID = dbo_B.A_ID;

If you're running Access with "SQL 92" compatibility mode turned on, you can do the more standard:

  SELECT *
  FROM dbo_A LEFT JOIN 
    (SELECT dbo_B.* FROM dbo_B WHERE dbo_B.F_ID = 3) AS dbo_B 
      ON dbo_A.ID = dbo_B.A_ID;

Do you need this to be editable in Access? If not, just use a passthrough query with the native T-SQL. If so, I would likely create a server-side view for this, and I'd especially want to move it server-side if the literal value is something you would parameterize (i.e., the F_ID=3 is really F_ID=N where N is a value chosen at runtime).

BTW, I write these subselect derived table SQL statements every single day while working in Access. It's not that big a deal.

Problem

I have the following T-SQL query (a simple test case) running fine in MS SQL but cannot get the equivalent query in MS Access (JET-SQL). The problem is the additional criteria in the LEFT JOIN. How can I do this in MS Access? T-SQL: ``` SELECT * FROM A LEFT OUTER JOIN B ON A.ID = B.A_ID AND B.F_ID = 3 ``` JET-SQL (what I have so far but crashes Access!): ``` SELECT * FROM dbo_A LEFT JOIN dbo_B ON (dbo_A.ID = dbo_B.A_ID AND dbo_B.F_ID = 3) ```

Original source