From every row from the main table, I want to obtain the ColWanted value from the mapping table. For example, for the main table row with id 100 I should obtain 'one', for the id 101 I should obtain 'two', for the 102 'one', 103 'one', etc.
Is it possible?
Main table
| PkId | CAT | UP |
|---|---|---|
| 100 | 1 | 1 |
| 101 | 1 | 2 |
| 102 | 2 | 1 |
| 103 | 1 | 3 |
Mapping table
| CAT | UP | ColWanted |
|---|---|---|
| 1 | null | one |
| 1 | 2 | two |
| 2 | null | one |
CodePudding user response：
An empty string or NULL in the MappingTable is considered matching any value.
select m.PkId, p.ColWanted
from MainTable m
left join MappingTable p on (m.CAT = p.CAT or p.CAT is null or p.CAT ='' )
and (m.UP = p.UP or p.UP is null or p.UP ='')
CodePudding user response：
Here's another solution:
SELECT m.PkId, p.ColWanted
FROM MainTable m
JOIN MappingTable p ON m.CAT = p.CAT AND m.UP = COALESCE(p.UP, m.UP)
Obvious m.UP = m.UP is guaranteed to be true. So when p.UP is NULL, the default is that the match is true.