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)

Mystical

Shouldn't need the brackets on the join criteria – Malva

Does it matter that the T-SQL example is an outer join? All its essentially doing is returning all rows in A. – Rom

@OMG Ponies - Without the parenthesis, I get a syntax error, with them, Access crashes... @Rom - No, the word OUTER is optional. – Mystical

Answer 1

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.

Titanite

Yes I know I can use a subselect, but it's significantly slower so I'd rather use a left join. I didn't know about the passthrough query option though, that worked perfectly! (more info for others here: support.microsoft.com/kb/303968). I am marking your answer as accepted since it worked. Thanks! – Mystical

Whether the subselect is slower depends on two things: 1) how the database engine involved optimizes the subselect vs. the alternative, and the obvious one in this case, 2) whether or not the option is even available. In Jet/ACE SQL it isn't because you can't have a multi-field join defined in the opposite direction (i.e., one from A=>B and the other from B=>A). It may be that SQL Server optimizes the subselect suboptimally in comparison toe the alternative, but if you're using Jet/ACE, you're going to have to follow Jet/ACE's rules, hence the mention of passthroughs. – Titanite

I am pretty sure a left join is always faster than a subselect. Sure, in a small dataset or if your DB engine can optimize (AKA turn your subselect into a left join) you won't see a difference, but "a left join then a select of n rows" is going to be faster than "n+1 selects". Definitely in my case SQL Server ran the left join faster. – Mystical

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.

Rita

Definitely a good idea, does that mean my syntax is correct just Access can't handle it? – Mystical

I don't know if Access itself has a technical limit, but if the tables are millions of rows then the PC on which Access is running as well as the network over which the data has to flow are probably being overwhelmed. – Rita

It's not true at all that Jet/ACE asks for the full table and does the joins itself, unless there's something preventing Jet/ACE from getting the metadata it needs to determine if it can pass the whole thing off to the server (which should be the case 99% of the time). Or, it could be that there is something about the linked tables (which could be views, for instance) that prevents Jet/ACE from doing the job. Those are the onlyl two cases I can think of that could cause Jet/ACE to request the full tables with the supplied SQL. In short, it's very, very unlikely that is going to happen. – Titanite

Creating a view is a great idea, but I don't have permissions to create views. – Mystical

Answer 3

I think ms access expect to both tables name in each section of Joins ON clause. As a trick this work for me:

SELECT * FROM A 
LEFT OUTER JOIN B ON A.ID = B.A_ID 
                 AND B.F_ID = IIF(True, 3, A.ID) 

A.ID or any other else field from table A

Glosso

Answer 4

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;

Rom

That's not true. Checking F_ID = 3 in the left join condition would give you null for all values from B when F_ID = 3. Putting it in the where clause wouldn't return them at all. – Ramulose

Sorry, what I meant was that you get null when F_ID <> 3 for a left join. – Ramulose