multi-condition left join conditions in MS access

Question

Is there another way to write the following poorly performing in Access (Jet) SQL?

SELECT *
FROM
  Det left join 
  (select * from Inv inner join Dep on Inv.SN=Dep.SN) as mass ON
    (Det.RecSN = mass.SelfID) and (Det.DDTime between mass.Start and mass.End)

eliminating the sub-select and doing a direct join does not work, giving a JOIN expression not supported Error in the following snippet

SELECT *
FROM
  Det left join 
  (Inv inner join Dep on Inv.SN=Dep.SN) ON
    (Det.RecSN = Inv.SelfID) and (Det.DDTime between Dep.Start and Dep.End)

The following produces a result, but suppresses Det records that don't match up to one or the other of the inner tables

SELECT *
FROM
  Det inner join 
  (Inv inner join Dep on Inv.SN=Dep.SN) ON
    (Det.RecSN = Inv.SelfID) and (Det.DDTime between Dep.Start and Dep.End)

adding another set of parentheses doesn't help other than helping Access highlight the join clause that's problematic

SELECT *
FROM
  Det left join 
  (Inv inner join Dep on Inv.SN=Dep.SN) ON
    ((Det.RecSN = Inv.SelfID) and (Det.DDTime between Dep.Start and Dep.End))

dropping the between clause to a where condition results in no left join characteristics (DDTime isn't between NULL and NULL) and can lead to too many entries being held in memory while other joins are being performed to this whole mess, and can make it more logically difficult to add other where conditionals.

SELECT *
FROM
  Det left join 
  (Inv inner join Dep on Inv.SN=Dep.SN) ON
    Det.RecSN = Inv.SelfID 
WHERE
  Det.DDTime between Dep.Start and Dep.End

Adding a clause to make left join behavior re-emerge (but only for the portion in the ON clause) makes the world slow to a crawl, I think because Access doesn't know how to handle OR clauses without essentially running a union all in the background for each of the sides of the OR clause with the rest of the query.

SELECT *
FROM
  Det left join 
  (Inv inner join Dep on Inv.SN=Dep.SN) ON
    Det.RecSN = Inv.SelfID 
WHERE
  Det.DDTime between Dep.Start and Dep.End
  OR Dep.Start is NULL

My current work-around is to use the inner join method, then have another query that queries this one ("qCombo") and compares against the Det table for entries in Det, but not in qCombo using either a left join or where not exists. But this solution is just as inefficient (if not more so) than the sub-select method at top.

SELECT 'qCombo' as QueryName, *
FROM
  Det inner join 
  (Inv inner join Dep on Inv.SN=Dep.SN) ON
    (Det.RecSN = Inv.SelfID) and (Det.DDTime between Dep.Start and Dep.End)

Select Det.*
FROM
  Det LEFT JOIN
  qCombo ON
    (Det.DDTime=qCombo.DDTime) and
    (Det.OtherFieldsInUniqueIdx=qCombo.OtherFieldsInUniqueIdx)
WHERE
  qCombo.QueryName IS NULL

Other possibilities?

Answer 1

Are the tables you are querying indexed?

If not, I would add an index to the "Inv.SN=Dep.SN"columns , " Det.RecSN = Inv.SelfID" and lastly the dates "Det.DDTime, Dep.Start".

NOTE: Be careful when adding indexes. Specially if you are working in your production database. Do your research first on creating indexes on transactional tables, to avoid locks.

If you already have these indexed, then how many records do you have on each table?

Suggestion: I would Separate the OR conditions in different queries and save into a temporary table, so that I can analyze the data sets and results. Ultimately, querying the temporary tables for the final join.

Rule of thumb:

query just one record Id, and see the performance. Always check if index exists.

There may be various issues, and multiple approaches.