ACCESS LEFT JOIN With Multiple Criteria

I'd like to do something like this: SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.id=t2.id AND t1.field1=false So, I don't want to see data from t2 table for those records from t1 t

Solution 1:

If you want to apply a filter to the dataset, try using WHERE in place of AND in your query.

SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t1.field1=False

Solution 2:

I managed to get the desired result with multiple SELECT, which is disgusting, if you have a long and multiple SELECT in the first place.

SELECT t1.*, a.c1 FROM t1 LEFT JOIN (SELECT t2.* FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t1.f1=false) AS a ON t1.id=a.id

Solution 3:

This simpler solution works for me:

SELECT t2.*, falset1.c1
FROM t2 LEFT JOIN (SELECT t1.*
                    WHERE t1.f1=false) AS falset1
ON t2.id=falset1.id

Basically you subset the data that needs a constant with the where clause and then use that subset in the left join