CODEWITHSUNDEEP
sqlsqlitejoinnull
kaki gadol
kaki gadol

Reputation: 1370

Join two tables, using value from the first unless it is null, otherwise use value from the second

I have three tables which look like those:

TABLE 1

id    j_id

1     1
2     2
3     3

TABLE 2

id    j_id     table1_id

1     57       1
2     84       1
3     1        1
4     9        2
5     2        2

and every j has a value in a third table

id    value

1     1abc
2     2bcd
3     3abc
57    57abc
84    84abc
9     9abc

I am trying to write a query which will join table 1 and table 2 and use the J value from the third table instead of the j_id, but the problem is that I want to use the j value from the second table if it exists and otherwise use the value from the first table.

in order the make it clearer this is my query result without using the third table:

tbl1.j_id    tbl2.j_id

1            1
1            84
1            57
2            2
2            9
3            null

I want the end query result to use the second table's j value unless it is null:

tbl1.j_id    tbl2.j_id    j_id

1            1            1abc
1            84           84abc
1            57           57abc
2            2            2abc
2            9            9abc
3            null         3abc

(Question and title edits are more than welcome, weren't that sure how to phrase them..)

Upvotes: 0

Views: 132

Answers (2)

Nick
Nick

Reputation: 147196

You can simply JOIN to table3 on the COALESCE of table2.j_id and table1.j_id:

SELECT t1.j_id AS t1_j_id, t2.j_id AS t2_j_id, t3.value
FROM table1 t1
LEFT JOIN table2 t2 ON t2.table1_id = t1.id
JOIN table3 t3 ON t3.id = COALESCE(t2.j_id, t1.j_id)

Output:

t1_j_id t2_j_id value
1       1       1abc
1       57      57abc
1       84      84abc
2       2       2bcd
2       9       9abc
3       null    3abc

Demo on dbfiddle

Upvotes: 2

GMB
GMB

Reputation: 222512

One solution is to left join table3 twice:

select
    t1.j_id,
    t2.j_id,
    coalesce(t31.value, t32.value) j_value
from 
    table1 t1
    left join table2 t2 on t2.table1_id = t1.id
    left join table3 t31 on t31.id = t2.j_id
    left join table3 t32 on t32.id = t1.j_id

Upvotes: 1

Related Questions