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 |
+-----+----------+
I think you want two
join
s, one to bring in the matching value (if any) and the other to bring in the default: