求一条SQL语句

TableA


id name type company
1 小明 type_a 公司1
2 小王 type_b 公司1




TableB  
id type_a type_b type_c company
1 11 43 88 公司1
2 33 55 78 公司2

想得到的结果

id name type company
1 小明 11 公司1
2 小王 43 公司1

---

SQL code:
select a.id,a.name,b.type,b.company
from tablea a,tableb
where a.id=b.id

---

SQL code:
select a.id,a.name,b.type_a as type,a.company from table1 a join table2 on a.company=b.company
union all
select a.id,a.name,b.type_b as type,a.company from table1 a join table2 on a.company=b.company

---

1楼的简洁，好
1楼的简洁，好

---

SQL code:

select id, name,
case a.when 'type_a' then b.type_a when 'type_b' then b.type_b else type_c end, company
from tablea a,tableb b
where a.id=b.id

---

引用

SQL code
select a.id,a.name,b.type_a as type,a.company from tablea a join tableb on a.company=b.company
union all
select a.id,a.name,b.type_b as type,a.compa