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
    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 '18 at 4:58
8

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')
| improve this answer | |
  • 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 '18 at 2:39
1

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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