CODEWITHSUNDEEP
sql
HellaF10
HellaF10

Reputation: 31

Left join values with where condition, multiple problems on missing entries

I thought i can get this Problem out with a simple left join, but it doesnt work.

Data:

Table 1        
**VALUE**
X23
FG21.21
:
GT31+

To get the resolved data from that values i have to left join an other table. The Problem is, that ':' and 'GT31+' are wrong. There dont exist in Table2 and nothing will be resolved, but they have to be in the output.

Table 2
**VALUE     DATE         NAME**
X23      '1992-02-01'    YELLOW
X23      '2999-12-31'    BLUE
FG21.21  '2005-12-08'    BLACK
FG21.21  '2999-12-31'    RED
:         **NO ENTRY IN THE TABLE!**
GT31+     **NO ENTRY IN THE TABLE!** 



SELECT a.VALUE as VAL, b.VALUE as VAL_B, b.NAME as NAME
FROM TABLE1 a
LEFT JOIN TABLE2 b on (a.value = b.value)
WHERE DATE = '2999-12-31'

My output is:

VAL       VAL_B      NAME
X23       X23        BLUE
FG21.21   FG21.21    RED

My desired output is

    VAL       VAL_B      NAME
    X23       X23        BLUE
    FG21.21   FG21.21    RED
    :         null       null
    GT31+     null       null

Upvotes: 0

Views: 26

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270763

Move the condition on the second table to the ON clause:

SELECT a.VALUE as VAL, b.VALUE as VAL_B, b.NAME as NAME
FROM TABLE1 a LEFT JOIN
     TABLE2 b 
     ON a.value = b.value AND b.DATE = '2999-12-31'

Also, qualify all column references so it is clear where the columns are coming from.

Upvotes: 1

juergen d
juergen d

Reputation: 204894

Your where condition turn your left join into an inner join because it filters on a column in table2.

Just put the condition directly in the join

SELECT a.VALUE as VAL, b.VALUE as VAL_B, b.NAME as NAME
FROM TABLE1 a
LEFT JOIN TABLE2 b ON a.value = b.value
                  AND b.DATE = '2999-12-31'

Upvotes: 2

Related Questions