Fix for MS Access LEFT JOIN : Join expression not supported

[rdhatch]

Problem: Cannot LEFT JOIN on x AND y when x and y are in different tables. "Join expression not supported" error.

PROVEN SOLUTION: If you want a true left join - where records are optional - and you're filtering by multiple fields in different tables - Then I have your answer.

To do filtering on fields from other tables - we need to use the WHERE clause. The key here is: You need to make the WHERE clause matches whether or not there are records in table you are LEFT JOINing. This is your answer:

WHERE (LEFTJOINTABLE.id IS NULL) OR (LEFTJOINTABLEid IS NOT NULL AND (__PUT YOUR JOIN FILTERS HERE__) )

Hope this helps you!

Ryan

THIS FIX REFERENCES:

http://www.tek-tips.com/viewthread.cfm?qid=285706

 

[rdhatch]

Here is the fix implemented as a function for use with LLBLGen in .NET 2.0:

Ryan

        Public Shared Function getLeftJoinWHEREClause(Of TEntity As {IEntity, EntityBase})(ByRef myLeftJoinEntity As TEntity, ByRef myLeftJoinFilters As PredicateExpression) As PredicateExpression
            'Cannot do a LEFT JOIN ON x AND y when x and y are in different tables.
            'The ON clause can only accept logic related to the join. 
            '[URL unfurl="true"]http://www.tek-tips.com/viewthread.cfm?qid=285706[/URL]
            'RYAN'S SOLUTION: make the WHERE clause match both ways

            Dim myFilter As New PredicateExpression

            'Need a Primary Key field to determine if the record is blank or not
            Dim myKey As EntityField = DirectCast(myLeftJoinEntity.PrimaryKeyFields(0), EntityField)

            'IS NULL
            myFilter.Add(myKey = System.DBNull.Value)

            'NOT NULL
            Dim myExistsFilter As New PredicateExpression
            myExistsFilter.Add(myKey <> System.DBNull.Value)
            myExistsFilter.AddWithAnd(myLeftJoinFilters)

            'Combine IS NULL OR NOT NULL
            myFilter.AddWithOr(myExistsFilter)

            Return myFilter
        End Function

 

[PHV]

I'd simply use the Nz function ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rdhatch]

Hi PH,

Could you please elaborate? As far as I know, the Nz function returns a value for a field that is NULL. How would I use the Nz(variant,value_when_null) function to simplify this query:

WHERE (LEFTJOINTABLE.id IS NULL) OR (LEFTJOINTABLEid IS NOT NULL AND (__PUT YOUR JOIN FILTERS HERE__) )

Thanks!

Ryan