Search code examples
sqlms-accessleft-join

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 table where t1.field1=false. It's possible in ORACLE, but is this possible in MS ACCESS?

Edit: I got Syntax error in JOIN operation and JOIN expression not supported and Invalid argument to function.

Edit2: To prevent further misunderstanding and "put in the WHERE clause" comments. <code>t1</code> table <code>t2</code> table

SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.id=t2.id simple <code>LEFT JOIN</code>

If you write my very first SELECT in ORACLE, you will see this (and I have to do it in MS). I want to see ALL records from t1 table, but I don't want to join every record, but only where t1.f1=false. You can see that where id=2 and id=5. how it works in ORACLE but not in MS

Solution

  • 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