LEFT JOINING by additional criteria in MS Access

Question

I have the following T-SQL query (a simple test case) that works fine in MS SQL but cannot get an equivalent query in MS Access (JET-SQL). The problem is the additional criteria in the LEFT JOIN. How to do it 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 (which I still have, but Access crashes!):

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

Answer 1

You need to use a subquery 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 use Access with the "SQL 92" compatibility mode enabled, you can make a more standard one:

  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 edited in Access? If not, just use the Passthrough query with native T-SQL. If so, I will most likely create a server-side view for this, and I would especially like to move it on the server side if the literal value is what you could parameterize (i.e. F_ID = 3 is really F_ID = N where N is the value selected at runtime).

By the way, I write these subtasks of derived SQL table statements every day while working in Access. This is not such a big deal.

Answer 2

Does the error message appear on failure or is it simply blocked? Judging by the name dbo_B, I'm going to assume that these are related tables in Access. I believe that when you make such a connection, Access does not tell the SQL server that it needs the result of the connection, it says: “Give me all the rows of both tables,” then he tries to join them himself. If the tables are very large, this can lead to application blocking.

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

Answer 3

This last condition is technically not a union, but a comparison with a literal meaning. Put this in the WHERE clause:

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