sql query for static and dynamic table using joins

Question

I'm trying to write a query for below table.

Table A contains dynamic data - ID, Category and type

+-----+----------+---------------+
| id  | category  | type         |
+-----+----------+---------------+
|   1 |      ETD | H49A          |
|   2 |      ETD | SZ8A          |
|   3 |      ETD | VQA5          |
|   4 |      ETD | Null          |
|   5 |      ETD |   NA          |
|   6 |      ETD |               |
|   - |        - |   -           |
|   - |        - |   -           |
|  16 |      OTC | BVX9A         |
|  17 |      OTC | KG4G          |
+-----+----------+---------------+

And Static table B contains mapping of category and type combinations to attributes:

+-----+----------+---------------+
| atr | category  | type         |
+-----+----------+---------------+
|   V |      ETD | H49A          |
|   W |      ETD | SZ8A          |
|   X |      ETD | NA            |
|   Y |      OTC | BVX9A         |
|   Z |      OTC | NA            |
|   - |       -  |   -           |
+-----+----------+---------------+

All categories present in table a present in table b.

and the result should look like this. query should be universal and depend on specific values in table a.

+-----+----------+
| id  |      atr | 
+-----+----------+
|   1 |       V  |
|   2 |       W  |
|   3 |       X  |
|   4 |       X  |
|   5 |       X  | 
|   6 |       X  |
|   - |        - | 
|   - |        - | 
|  16 |       Y  |
|  17 |       Z  |
+-----+----------+

Answer 1

I think you want two joins, one to bring in the matching value (if any) and the other to bring in the default:

select a.id, coalesce(b.atr, bdef.atr)
from a left join
     b
     on a.category = b.category and
        a.type = b.type left join
     b bdef
     on a.category = b.category and b.type = 'NA'