Left join coalesce returns null values when there value

Options
ShrimpMania
ShrimpMania Registered Posts: 15

I have a table1 that specialty code B doesn't have company name. Since I needed to make the dataset to have all date(2020-01, 02,03 and 04), I had to make a table that has all the months and a column that all rows have 0(zero_col). I used cartesian join for that. After that, I tried to left join and select coalesce(value, zero_col) where specialty_code, product and company type are the same but the highlighted rows returns 0 even though I expected the highlighted row will return its values 1100 and 800 but it returns 0.

Shouldn't it return the value since both company name are empty and it recognise as it's the same?

table1:

specialty codeproductcompanytypedatevalue
Achocolatem&m'sgrocery2020-01-01T00:00:00.000Z3300
Bpasta grocery2020-01-01T00:00:00.000Z1100
Bpasta grocery2020-03-01T00:00:00.000Z800
Elaptophpelectric2020-01-01T00:00:00.000Z8800
Elaptophpelectric2020-02-01T00:00:00.000Z11000
Elaptophpelectric2020-03-01T00:00:00.000Z8800
Elaptophpelectric2020-04-01T00:00:00.000Z7000

Output

specialty codeproductcompanytypedatevalue
Achocolatem&m'sgrocery2020-01-01T00:00:00.000Z3300
Achocolatem&m'sgrocery2020-02-01T00:00:00.000Z0
Achocolatem&m'sgrocery2020-03-01T00:00:00.000Z0
Achocolatem&m'sgrocery2020-04-01T00:00:00.000Z0
Bpasta grocery2020-01-01T00:00:00.000Z0
Bpasta grocery2020-02-01T00:00:00.000Z0
Bpasta grocery2020-03-01T00:00:00.000Z0
Bpasta grocery2020-04-01T00:00:00.000Z0
Elaptophpelectric2020-01-01T00:00:00.000Z8800
Elaptophpelectric2020-02-01T00:00:00.000Z11000
Elaptophpelectric2020-03-01T00:00:00.000Z8800
Elaptophpelectric2020-04-01T00:00:00.000Z7000

Tagged:

Answers

Setup Info
    Tags
      Help me…