LEFT JOIN with WHERE (Microsoft Access SQL)

Question

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 (())?

Answer 1

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')

Answer 2

Here create an alias of specific language table as c.

SELECT a.ID, a.job, c.job FROM a
LEFT JOIN 
(SELECT * FROM b WHERE lang='de') as c
ON a.ID=c.ID