Conditional operations without using SWITCH CASE

Question

I have a couple of complex tables. But their mapping is something like:

TABLE_A:

_________________________________
|   LINK_ID     |   TYPE_ID     |
_________________________________
| adfasdnf23454 |   TYPE 1      |
| 43fsdfsdcxsh7 |   TYPE 1      |
| dfkng037sdfbd |   TYPE 1      |
| sd09734fdfhsf |   TYPE 2      |
| khsadf94u5dfc |   TYPE 2      |
| piukvih66wfa8 |   TYPE 3      |
_________________________________

TABLE_B:

_____________________________________________
|   LINK_ID     |  CODE_ID  | CODE_VALUE    |
_____________________________________________
| adfasdnf23454 |    7      |   test 1      |
| fgubk65esdfj7 |    6      |   test 2      |
| ooogfsg354fds |    7      |   test 3      |
| sd09734fdfhsf |    5      |   test 4      |
_____________________________________________

The LINK_ID column links these two tables.

My requirement is to have all the records from TABLE_A checked whether they have a specific CODE_ID or not.

1. If the record has CODE_ID as 7 - populate CODE_VALUE in a column.
2. If the record has CODE_ID as 6 - populate CODE_VALUE in another column.
3. If the record doesn't have a CODE_ID show CODE_VALUE as null.

The catch is, TABLE_B may have records that TABLE_A don't. But the final result should contain the records of TABLE_A alone.

PS: SWITCH CASE not suggested since I would require the fields in the same row. (Please see the multiple rows for same LINK_ID in OBTAINED RESULT on using SWITCH CASE)

OBTAINED RESULT:

_______________________________________________
|   LINK_ID     | CODE_VALUE_1 | CODE_VALUE_1 |
_______________________________________________
| adfasdnf23454 |   test 1     |    null      |
| adfasdnf23454 |   null       |    test 4    |
| sd09734fdfhsf |   test 6     |    null      |
_______________________________________________

EXPECTED RESULT:

__________________________________________________
|   LINK_ID     | CODE_VALUE_1  |   CODE_VALUE_2 |
__________________________________________________
| adfasdnf23454 |   test 1      |   test 4       |
| 43fsdfsdcxsh7 |   null        |   null         |
| dfkng037sdfbd |   null        |   null         |
| sd09734fdfhsf |   test 6      |   null         |
| khsadf94u5dfc |   null        |   null         |
| piukvih66wfa8 |   null        |   null         |
__________________________________________________

Can someone help on this?

Answer 1

One option uses two correlated subqueries:

select
    a.link_id,
    (select code_value from table_b b where b.link_id = a.link_id and b.code_id = 7) code_value_1,
    (select code_value from table_b b where b.link_id = a.link_id and b.code_id = 6) code_value_2
from table_a a

Note that this assumes no duplicate (link_id, code_id) in table_b. You could also write this with two left joins - which is quite the same logic.

Another solution is a single left join, then conditional aggregation:

select  
    a.link_id,
    max(case when b.code_id = 7 then b.code_value end) code_value_1,
    max(case when b.code_id = 6 then b.code_value end) code_value_2
from table_a a
left join table_b b on b.link_id = a.link_id and b.code_id in (6, 7)
group by a.link_id