multi-condition left join conditions in MS access

3 votes
2 answers
3564 views
ms-access-2016
                          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?

                        
Asked by mpag (978 rep)
Jul 18, 2018, 11:18 PM
Last activity: Apr 16, 2025, 06:00 PM
multi-condition left join conditions in MS access

Related Questions
