0

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

1
  • 1
    Left join tries to add NULL-valued parts for right table when corresponding record(s) not exists... but then your condition in WHERE ejects all of them. And LEFT JOIN acts as INNER JOIN (with a lot of additional work). Remember - if you want any condition for right table, it is to be placed into ON section.
    – Akina
    Aug 20, 2018 at 4:58

2 Answers 2

7

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')
2
  • 1
    For Access SQL, the composite ON condition must be within parenthesis like ON (a.ID=b.ID and b.lang='de')
    – C Perkins
    Aug 21, 2018 at 2:39
  • 1
    That works great in T-SQL or probably any other flavour of SQL, but I get "JOIN expression not supported" whenever I try to use a literal in the ON condition in Microsoft Access. It doesn't appear to be supported by Access SQL: learn.microsoft.com/en-us/office/vba/access/concepts/… Nov 7, 2020 at 12:39
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
1
  • This solutions works perfectly in Microsoft Access. Thanks! Nov 7, 2020 at 12:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.