LEFT JOINing on additional criteria in MS Access

Question

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)

Answer 1

That last condition technically isn't a join but a comparison to a literal value. Put it in a WHERE clause:

SELECT *
FROM a LEFT OUTER JOIN b ON a.ID = b.a_id
WHERE b.f_id = 3;

Answer 2

Do you get an error message when it crashes or does it just lock up? Judging by the dbo_B name I'm going to guess that these are linked tables in Access. I believe that when you do a join like that Access doesn't tell SQL server that it needs the result of the join, it says, "Give me all of the rows of both tables" then it tries to join them itself. If the tables are very large this can cause the application to lock up.

You're probably better off creating a view on SQL Server for what you need.

Answer 3

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.