LEFT JOIN with WHERE (Microsoft Access SQL) Given following 2 tables a, b:

table a

ID job
1  farmer
2  physician
3  bank clerk


table b

bID ID lang job
1   1  en   farmer
2   2  en   physician
3   3  en   bank clerk
4   2  de   Arzt
5   1  fr   paysan


Query

SELECT a.ID, a.job, b.job FROM a LEFT JOIN b ON a.ID=b.ID WHERE b.lang='de'


wanted:

a.ID a.job        b.job
1    farmer       -
2    physician    Arzt
3    bank clerk   -


actual result:

2    physician    Arzt


What's wrong with the LEFT JOIN? Do I need some (())?
In your query left join is performed only using only the condition a.ID=b.ID and then results are filtered based on condition b.lang='de'. You need to add both conditions on the left join:

SELECT a.ID, a.job, b.job 
FROM a LEFT JOIN b 
ON (a.ID=b.ID and b.lang='de')