0

In my first Table which is a result of calculations before i got names of some Items. In my 2nd Table i got all translations (de, en, ...). Problem is there are missing some of the translations and Id´s. My Code

SELECT
    R.id,
    R.name,
    R.count,
    limit2,
    max_ts AS reset_ts,
    ts AS lastcount_ts,
    estimation,
    TL.text as translate
FROM
    relevant_data R
LEFT JOIN 
    identifier_texts TL
    ON
        TL.identifier_id = R.id
    WHERE
        TL.language = 'de'

If id´s are missing the result is Nothing. So is there a way to say if id is missing take another value for example the original text without translation. I´ve tried it with NOT EXISTS and anything else nothing worked.

Result if all id´s available:

id      name        count       limit2  reset_ts            lastcount_ts  estimation    translate
3314    PunchPSU1   11863421    800000  25.02.2020 11:44    07.07.2020    2020-03-05    Querstanze (KHE) 1
3315    PunchPSU2   11863436    800000  25.02.2020 11:44    07.07.2020    2020-03-05    Querstanze (KHE) 2
3316    PunchPSU3   12118854    800000  25.02.2020 11:44    07.07.2020    2020-03-05    Querstanze (KHE) 3
3317    PunchPSU4   12119034    800000  25.02.2020 11:44    07.07.2020    2020-03-05    Querstanze (KHE) 4

so if id is missing or all result could be like this

id      name        count       limit2  reset_ts            lastcount_ts  estimation    translate
3314    PunchPSU1   11863421    800000  25.02.2020 11:44    07.07.2020    2020-03-05    PunchPSU1
3315    PunchPSU2   11863436    800000  25.02.2020 11:44    07.07.2020    2020-03-05    PunchPSU2
3316    PunchPSU3   12118854    800000  25.02.2020 11:44    07.07.2020    2020-03-05    PunchPSU3
3317    PunchPSU4   12119034    800000  25.02.2020 11:44    07.07.2020    2020-03-05    PunchPSU4

or translation is emtpy i dont care. There must be a way to do so. Searched nearly eveything but nothing seems to work

s0Nic
  • 97
  • 6

1 Answers1

1

Move the criteria on the second table in the WHERE clause to the ON clause of the join:

SELECT
    R.id,
    R.name,
    R.count,
    limit2,
    max_ts AS reset_ts,
    ts AS lastcount_ts,
    estimation,
    TL.text as translate
FROM relevant_data R
LEFT JOIN identifier_texts TL
    ON TL.identifier_id = R.id AND TL.language = 'de';

The problem with your current query is that the WHERE restriction happens after the join. As a result, the restriction on language to German, when in the WHERE clause, would cause records not having German to be filtered off prematurely. Using the version I gave above gets around this problem, and retains all records on the left side of the join, even if they do not match.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360